Find completely overlapping key (primary key and unique) constraints. This is a form of duplication. It leads to the creation of multiple indexes to the same set of columns.
Notes
The query takes into account that the order of columns in different keys could be different. For instance, the query considers constraints PRIMARY KEY (a, b) and UNIQUE (b, a) as duplicates. In case of the string_agg function, the line break (br) tag is used as a part of the separator for the better readability in case the query result is displayed in a web browser. PostgreSQL 14 added primary keys, unique constraints, and foreign keys to system catalogs. Thus the query was modified to exclude the results from the system catalog.
Type
Problem detection (Each row in the result could represent a flaw in the design)
All but one are redundant. Drop redundant constraints. It will cause automatic dropping of supporting indexes. If there are both PRIMARY KEY and UNIQUE constraints, then drop the UNIQUE constraint. If there are two UNIQUE constraints, then drop one of these. The particular statement chooses the UNIQUE constraint that name comes after in the alphabetical order.
Data Source
system catalog only
SQL Query
with keys as (select
o.conname,
nc.nspname as key_schema,
c.relname as key_table,
c.oid as key_table_oid,
o.conkey AS key_col,
case when o.contype='p' then 'PRIMARY KEY' ELSE 'UNIQUE' END as contype
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
inner join pg_namespace as nc on nc.oid=c.relnamespace
inner join pg_authid AS a on nc.nspowner=a.oid
where (nc.nspname='public' or rolname<>'postgres')
AND o.contype in ('u', 'p')),
keys_unnest as (select conname, key_schema, key_table, key_table_oid, key_col, key_col_num, ordin, contype
from keys, unnest(keys.key_col) with ordinality as k(key_col_num, ordin)),
keys_with_names as (select conname, key_schema, key_table, contype, string_agg(a_key.attname, ',' order by attname) as key_col, array_agg(a_key.attname order by ordin) as key_col_ordinal
from keys_unnest k inner join pg_attribute a_key on k.key_col_num = a_key.attnum and k.key_table_oid = a_key.attrelid and a_key.attisdropped = false
group by conname, key_schema, key_table, contype)
select key_schema, key_table, key_col, Count(*) AS number_of_duplicates, string_agg(conname || '.' || key_col_ordinal::text, '; ' order by conname) as keys
from keys_with_names
group by key_schema, key_table, key_col
having Count(*)>1
order by Count(*) DESC, key_col;
SQL statements that help generate fixes for the identified problem.
SQL Query to Generate Fix
Description
with keys as (select
o.conname,
(select nspname from pg_namespace where oid=m.relnamespace) as key_schema,
m.relname as key_table,
m.oid as key_table_oid,
o.conkey AS key_col,
case when o.contype='p' then 'PRIMARY KEY' ELSE 'UNIQUE' END as contype
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
inner join pg_class m on m.oid = o.conrelid
where o.contype in ('p','u') and o.conrelid in (select oid from pg_class c where c.relkind = 'r')),
keys_unnest as (select conname, key_schema, key_table, key_table_oid, key_col, key_col_num, ordin, contype
from keys, unnest(keys.key_col) with ordinality as k(key_col_num, ordin)),
keys_with_names as (select conname, key_schema, key_table, contype, array_agg(a_key.attname order by ordin) as key_col
from keys_unnest k inner join pg_attribute a_key on k.key_col_num = a_key.attnum and k.key_table_oid = a_key.attrelid and a_key.attisdropped = false
group by conname, key_schema, key_table, contype)
SELECT format ('ALTER TABLE %1$I.%2$I DROP CONSTRAINT %3$I;', a.key_schema, a.key_table, CASE WHEN a.contype='PRIMARY KEY' THEN b.conname ELSE a.conname END) AS statements
from keys_with_names as a, keys_with_names as b
WHERE a.key_schema=b.key_schema AND a.key_table=b.key_table AND
a.conname>b.conname AND
(a.key_col@>b.key_col AND b.key_col@>a.key_col)
UNION SELECT format ('ALTER TABLE %1$I.%2$I DROP CONSTRAINT %3$I;', a.key_schema, a.key_table, CASE WHEN a.contype='PRIMARY KEY' THEN b.conname ELSE a.conname END) AS statements
from keys_with_names as a, keys_with_names as b
WHERE a.key_schema=b.key_schema AND a.key_table=b.key_table AND
a.conname>b.conname AND
(a.key_col@>b.key_col AND b.key_col@>a.key_col)
ORDER BY statements;
Drop the constraint.
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
Duplication of implementation elements
Queries of this catergory provide information about the duplication of the database objects.
Uniqueness
Queries of this category provide information about uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) as well as unique indexes.