Duplicate keys

Goal	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)
Reliability	High (Few or no false-positive results)
License	MIT (opens in new tab)
Fixing Suggestion	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.

Fix Action #1

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.

SQL Query to Generate Fix Description

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.

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:

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:

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.

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.

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)