Find composite types with the same attributes (regardless of the order of attributes). Make sure that there is no duplication.
Notes
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.
Type
Problem detection (Each row in the result could represent a flaw in the design)
Drop the duplicate types. All but one are redundant.
Data Source
system catalog only
SQL Query
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 statements that help generate fixes for the identified problem.
SQL Query to Generate Fix
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.
Collections
This query belongs to the following collections:
Name
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 .
Categories
This query is classified under the following categories:
Name
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.