Enumerated or range types with the same name in different schemas

Goal	This query enforces the Don't Repeat Yorself principle across the database's type system. It identifies ENUM and RANGE types that share the same name but exist in different schemas. This indicates that a conceptual data type has been defined multiple times instead of having a single, canonical definition in a shared schema. Such duplication leads to maintenance overhead and the risk of semantic divergence over time.
Type	Problem detection (Each row in the result could represent a flaw in the design)
Reliability	High (Few or no false-positive results)
License	MIT (opens in new tab)
Fixing Suggestion	Rename a type. In case of duplication drop the duplicates and refactor the database so that the remaining type will be used everywhere it is needed.
Data Source	INFORMATION_SCHEMA+system catalog
SQL Query	WITH types AS (SELECT nspname AS type_schema, typname AS type_name, typtype FROM pg_catalog.pg_type t INNER JOIN pg_catalog.pg_namespace n ON t.typnamespace=n.oid WHERE typtype IN ('e','r') AND nspname NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL)) SELECT type_name, string_agg(type_schema \|\| '(' \|\| CASE WHEN typtype='e' THEN 'ENUMERATED' ELSE 'RANGE' END \|\| ')',';' ORDER BY type_schema) AS schemas, Count() AS number_of_types FROM types GROUP BY type_name HAVING Count()>1 ORDER BY type_name;

SQL statements that help generate fixes for the identified problem.

Fix Action #1

WITH types AS (SELECT nspname AS type_schema, typname AS type_name
FROM pg_catalog.pg_type t INNER JOIN pg_catalog.pg_namespace n ON t.typnamespace=n.oid
WHERE typtype IN ('e','r') AND nspname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)),
duplicates AS (SELECT type_name
FROM types
GROUP BY type_name
HAVING Count(*)>1)
SELECT format('DROP TYPE %1$I.%2$I;', type_schema, type_name) AS statements
FROM types
WHERE type_name IN (SELECT type_name
FROM duplicates)
ORDER BY type_schema, type_name;

Drop the duplicates.

SQL Query to Generate Fix Description

SQL Query to Generate Fix	Description
WITH types AS (SELECT nspname AS type_schema, typname AS type_name FROM pg_catalog.pg_type t INNER JOIN pg_catalog.pg_namespace n ON t.typnamespace=n.oid WHERE typtype IN ('e','r') AND nspname NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL)), duplicates AS (SELECT type_name FROM types GROUP BY type_name HAVING Count(*)>1) SELECT format('DROP TYPE %1$I.%2$I;', type_schema, type_name) AS statements FROM types WHERE type_name IN (SELECT type_name FROM duplicates) ORDER BY type_schema, type_name;	Drop the duplicates.

WITH types AS (SELECT nspname AS type_schema, typname AS type_name
FROM pg_catalog.pg_type t INNER JOIN pg_catalog.pg_namespace n ON t.typnamespace=n.oid
WHERE typtype IN ('e','r') AND nspname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)),
duplicates AS (SELECT type_name
FROM types
GROUP BY type_name
HAVING Count(*)>1)
SELECT format('DROP TYPE %1$I.%2$I;', type_schema, type_name) AS statements
FROM types
WHERE type_name IN (SELECT type_name
FROM duplicates)
ORDER BY type_schema, type_name;

Drop the duplicates.

Collections

This query belongs to the following collections:

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 .

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:

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.

Naming

Queries of this category provide information about the style of naming.

Range types

Queries of this category provide information about the use of range types

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.
Naming	Queries of this category provide information about the style of naming.
Range types	Queries of this category provide information about the use of range types

Reference
https://refactoring.guru/smells/alternative-classes-with-different-interfaces
https://refactoring.guru/smells/duplicate-code
The corresponding code smell in case of cleaning code is "G5: Duplication". (Robert C. Martin, Clean Code)