Query goal: | Find composite types with the same attributes (regardless of the order of attributes). Make sure that there is no duplication. |
Notes about the query: | The query takes into account that the order of attributes in different composite types could be different. For instance, the query considers types t1(a int, b varchar(10)) and t2(b varchar(10), a int) as duplicates. In case of each independent composite type the query forms a list of attributes that has been sorted based on the attribute names and uses it as the basis for grouping. |
Query type: | Problem detection (Each row in the result could represent a flaw in the design) |
Query reliability: | Medium (Medium number of false-positive results) |
Query license: | MIT License |
Fixing suggestion: | Drop the duplicate types. All but one are redundant. |
Data source: | system catalog only |
SQL query: | Click on query to copy it
WITH independent_composite_types AS (SELECT n.nspname AS type_schema, t.typname AS type_name, (SELECT string_agg(attname || ' ' || typname || CASE WHEN atttypmod=-1 THEN '' ELSE '(' || atttypmod || ')' END, ', ' ORDER BY attname) AS type_structure FROM pg_attribute INNER JOIN pg_type ON pg_attribute.atttypid=pg_type.oid WHERE pg_attribute.attrelid=t.typrelid AND pg_attribute.attisdropped='f' AND pg_attribute.attnum>0) AS type_structure FROM pg_type AS t INNER JOIN pg_namespace AS n ON n.oid=t.typnamespace INNER JOIN pg_authid AS a ON n.nspowner=a.oid WHERE (n.nspname='public' OR rolname<>'postgres') AND t.typtype='c' AND NOT EXISTS (SELECT 1 FROM pg_class WHERE pg_class.reltype=t.oid AND pg_class.relkind<>'c')) SELECT type_structure, Count(*) AS number_of_types, string_agg( type_schema || '.' || type_name, ', ' ORDER BY type_schema, type_name) AS types FROM independent_composite_types GROUP BY type_structure HAVING Count(*)>1 ORDER BY Count(*) DESC; |
SQL query | Description |
---|---|
WITH independent_composite_types AS (SELECT n.nspname AS type_schema, t.typname AS type_name, (SELECT string_agg(attname || ' ' || typname || CASE WHEN atttypmod=-1 THEN '' ELSE '(' || atttypmod || ')' END, ', ' ORDER BY attname) AS type_structure FROM pg_attribute INNER JOIN pg_type ON pg_attribute.atttypid=pg_type.oid WHERE pg_attribute.attrelid=t.typrelid AND pg_attribute.attisdropped='f' AND pg_attribute.attnum>0) AS type_structure FROM pg_type AS t INNER JOIN pg_namespace AS n ON n.oid=t.typnamespace INNER JOIN pg_authid AS a ON n.nspowner=a.oid WHERE (n.nspname='public' OR rolname<>'postgres') AND t.typtype='c' AND NOT EXISTS (SELECT 1 FROM pg_class WHERE pg_class.reltype=t.oid AND pg_class.relkind<>'c')), duplicate_composite_types AS (SELECT type_structure, Count(*) AS number_of_types, array_agg(type_schema || '.' || type_name) AS type_array FROM independent_composite_types GROUP BY type_structure HAVING Count(*)>1) SELECT format('DROP TYPE %1$s;', unnest(type_array)) AS statements FROM duplicate_composite_types ORDER BY type_structure; | Drop the type. |
Collection name | Collection description |
---|---|
Find problems automatically | Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not . |
Category name | Category description |
---|---|
Comfortability of database evolution | Queries of this category provide information about the means that influence database evolution. |
Data types | Queries of this category provide information about the data types and their usage. |
Duplication of implementation elements | Queries of this catergory provide information about the duplication of the database objects. |
User-defined types | Queries of this category provide information about user-defined types in the database. |