This query identifies semantic abstraction violations where data uniqueness is enforced via low-level CREATE UNIQUE INDEX statements instead of declarative PRIMARY KEY, UNIQUE, or EXCLUDE constraints. It specifically targets "plain" unique indexes (excluding partial or expression-based indexes) that are functionally identical to standard constraints. According to the ANSI-SPARC architecture, indexes belong to the internal (physical) schema, while constraints belong to the conceptual schema. Therefore, defining business rules using high-level constraint syntax is preferred for semantic clarity and architectural correctness.
Notes
The query uses a system view and returns CREATE INDEX statement.
Type
Problem detection (Each row in the result could represent a flaw in the design)
Drop the index and declare PRIMARY KEY or UNIQUE constraint instead.
Data Source
INFORMATION_SCHEMA+system catalog
SQL Query
SELECT schemaname, tablename, indexname, indexdef
FROM pg_catalog.pg_indexes
WHERE indexdef LIKE 'CREATE UNIQUE INDEX%' AND
indexdef NOT ILIKE '% WHERE %' AND
indexdef NOT ILIKE '%(lower(%' AND
indexdef NOT ILIKE '%(upper(%' AND
indexdef NOT ILIKE '%coalesce(%' AND
schemaname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL) AND
(schemaname, tablename, indexname) NOT IN
(select
(select nspname from pg_namespace where oid=c.relnamespace) as table_schema,
c.relname as table_name,
o.conname as constraint_name
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
where o.contype in ('p','u','x')
and c.relkind = 'r')
ORDER BY schemaname, tablename, indexname;
SQL statements that help generate fixes for the identified problem.
SQL Query to Generate Fix
Description
SELECT format('DROP INDEX %1$I.%2$I;', schemaname, indexname) AS statements
FROM pg_catalog.pg_indexes
WHERE indexdef LIKE 'CREATE UNIQUE INDEX%' AND
indexdef NOT LIKE '% WHERE %' AND
indexdef NOT LIKE '%(lower(%' AND
indexdef NOT LIKE '%(upper(%' AND
schemaname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL) AND
(schemaname, tablename, indexname) NOT IN
(SELECT
table_constraints.table_schema,
table_constraints.table_name,
table_constraints.constraint_name
FROM
information_schema.table_constraints
WHERE
table_constraints.constraint_type IN ('PRIMARY KEY', 'UNIQUE'))
ORDER BY schemaname, tablename, indexname;
Drop the index.
Collections
This query belongs to the following collections:
Name
Description
Find problems about integrity constraints
A selection of queries that return information about the state of integrity constraints in the datadabase. Contains all the types of queries - problem detection, software measure, and general overview
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.
Performance
Queries of this category provide information about indexes in a database.
Uniqueness
Queries of this category provide information about uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) as well as unique indexes.