Patterns of the names of PRIMARY KEY, UNIQUE, CHECK, EXCLUDE, and FOREIGN KEY constraints as well as user-defined non-unique indexes that are associated with exactly one column

WITH key_constraint_names AS (
select 
CASE WHEN contype='p' THEN 'PRIMARY KEY' ELSE 'UNIQUE' END AS type,
o.conname AS constraint_name,
c.relname as table_name, 
(select a.attname from pg_attribute a where a.attrelid = c.oid and a.attnum = o.conkey[1] and a.attisdropped = false) as column_name
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')  and cardinality(o.conkey)=1),

chk_constraint_names AS (select 
'CHECK' AS type,
o.conname AS constraint_name,
c.relname as table_name, 
(select a.attname from pg_attribute a where a.attrelid = c.oid and a.attnum = o.conkey[1] and a.attisdropped = false) as column_name
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 = 'c'  and cardinality(o.conkey)=1),

exclude_constraint_names AS (select 
'EXCLUDE' AS type,
o.conname AS constraint_name,
c.relname as table_name, 
coalesce((select a.attname from pg_attribute a where a.attrelid = c.oid and a.attnum = o.conkey[1] and a.attisdropped = false),'') as column_name
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 = 'x'  and cardinality(o.conkey)=1),

fk_constraint_names AS (select 
'FOREIGN KEY' AS type,
o.conname,
(select nspname from pg_namespace where oid=f.relnamespace) as foreign_ns,
f.relname as foreign_table,
(select a.attname from pg_attribute a where a.attrelid = f.oid and a.attnum = o.confkey[1] and a.attisdropped = false) as foreign_colname,
(select nspname from pg_namespace where oid=c.relnamespace) as target_ns,
c.relname as target_table, 
(select a.attname from pg_attribute a where a.attrelid = c.oid and a.attnum = o.conkey[1] and a.attisdropped = false) as target_colname
from pg_constraint o inner join   pg_class c on c.oid = o.conrelid
inner join pg_class f on f.oid = o.confrelid
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 = 'f'  and cardinality(o.conkey)=1),

index_without_col_name AS (select 
'INDEX' AS type,
ci.relname as index_name,
n.nspname as schema_name,
i.indrelid as table_oid,
ct.relname as table_name,
unnest(indkey) as col_seq
from pg_catalog.pg_index i inner join pg_catalog.pg_class ci on i.indexrelid=ci.oid
inner join pg_catalog.pg_class ct on i.indrelid=ct.oid
inner join pg_catalog.pg_namespace n on ct.relnamespace=n.oid
where indnatts=1 and indisunique=false and 
n.nspname  NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)),

index_with_col_name AS (
select type, index_name, schema_name, table_name, a.attname as column_name
from index_without_col_name i inner join pg_catalog.pg_attribute a on i.table_oid=a.attrelid and i.col_seq=a.attnum)

SELECT pattern, string_agg(DISTINCT type,';' ORDER BY type DESC) AS types, Count(*) AS number_of_occurrences, CASE WHEN Count(*)<5 THEN string_agg(constraint_name || ' (' || pattern_source || ')',';<br>' ORDER BY constraint_name ASC) ELSE NULL END AS names
FROM (SELECT 
replace(regexp_replace(lower(constraint_name), lower(table_name),'<b>table</b>'), lower(column_name),'<b>column</b>') AS pattern, type, constraint_name, table_name || '.' || column_name AS pattern_source
FROM key_constraint_names

UNION ALL SELECT replace(regexp_replace(lower(constraint_name), lower(table_name),'<b>table</b>'), lower(column_name),'<b>column</b>') AS pattern, type, constraint_name, 
case when column_name='' then table_name else table_name || '.' || column_name end AS pattern_source
FROM exclude_constraint_names

UNION ALL SELECT replace(regexp_replace(lower(index_name), lower(table_name),'<b>table</b>'), lower(column_name),'<b>column</b>') AS pattern, type, index_name, table_name || '.' || column_name AS pattern_source
FROM index_with_col_name

UNION ALL SELECT replace(regexp_replace(lower(constraint_name), lower(table_name),'<b>table</b>'), lower(column_name),'<b>column</b>') AS pattern, type, constraint_name, table_name || '.' || column_name AS pattern_source
FROM chk_constraint_names

UNION ALL SELECT 
regexp_replace(regexp_replace(lower(conname), lower(target_table),'<b>table</b>'),lower(foreign_table),'<b>foreign_table</b>') AS pattern, 
type, conname, 'primary: ' || foreign_table || '.' || foreign_colname || ' child: '  || target_table || '.' || target_colname AS pattern_source
FROM fk_constraint_names

) AS foo
GROUP BY pattern
ORDER BY types, Count(*) DESC, pattern;