Find completely overlapping key (primary key, unique, and exclude where all operators are =) 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 a.rolname <> 'postgres')
AND o.contype IN ('u', 'p')
UNION ALL
SELECT
o.conname,
nc.nspname AS key_schema,
c.relname AS key_table,
c.oid AS key_table_oid,
i.indkey AS key_col,
'EXCLUDE' 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
INNER JOIN
pg_index AS i ON i.indexrelid = o.conindid
WHERE
(nc.nspname = 'public' OR a.rolname <> 'postgres')
AND o.contype = 'x'
AND (
SELECT bool_and(op.oprname = '=')
FROM unnest(o.conexclop) AS op_oid(oid)
INNER JOIN pg_operator AS op ON op.oid = op_oid.oid
)),
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 a.key_schema as schema, a.key_table AS table, a.contype AS superkey_type, a.conname as superkey_constraint_name, a.key_col AS superkey_col,
b.contype AS candidate_key_type, b.conname AS candidate_key_constraint_name, b.key_col AS candidate_key_col
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 a.key_schema, a.conname, a.contype;
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.