The list of all the queries

Duplicate independent (i.e., not created based on a table) composite types

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 statements for generating SQL statements that help us to fix the problem

SQL queryDescription
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 where the query belongs to

Collection nameCollection description
Find problems automaticallyQueries, 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 where the query belongs to

Category nameCategory description
Comfortability of database evolutionQueries of this category provide information about the means that influence database evolution.
Data typesQueries of this category provide information about the data types and their usage.
Duplication of implementation elementsQueries of this catergory provide information about the duplication of the database objects.
User-defined typesQueries of this category provide information about user-defined types in the database.

The list of all the queries