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,
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_name1,
coalesce((select a.attname from pg_attribute a where a.attrelid = c.oid and a.attnum = o.conkey[2] and a.attisdropped = false),'') as column_name2,
coalesce((select a.attname from pg_attribute a where a.attrelid = c.oid and a.attnum = o.conkey[3] and a.attisdropped = false),'') as column_name3
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) IN (2,3)),
chk_constraint_names AS (select
'CHECK' 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_name1,
coalesce((select a.attname from pg_attribute a where a.attrelid = c.oid and a.attnum = o.conkey[2] and a.attisdropped = false),'') as column_name2,
coalesce((select a.attname from pg_attribute a where a.attrelid = c.oid and a.attnum = o.conkey[3] and a.attisdropped = false),'') as column_name3
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) IN (2,3)),
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_name1,
coalesce((select a.attname from pg_attribute a where a.attrelid = c.oid and a.attnum = o.conkey[2] and a.attisdropped = false),'') as column_name2,
coalesce((select a.attname from pg_attribute a where a.attrelid = c.oid and a.attnum = o.conkey[3] and a.attisdropped = false),'') as column_name3
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) IN (2,3)),
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,
coalesce((select a.attname from pg_attribute a where a.attrelid = f.oid and a.attnum = o.confkey[1] and a.attisdropped = false),'') as foreign_colname1,
coalesce((select a.attname from pg_attribute a where a.attrelid = f.oid and a.attnum = o.confkey[2] and a.attisdropped = false),'') as foreign_colname2,
coalesce((select a.attname from pg_attribute a where a.attrelid = f.oid and a.attnum = o.confkey[3] and a.attisdropped = false),'') as foreign_colname3,
(select nspname from pg_namespace where oid=c.relnamespace) as target_ns,
c.relname as target_table,
coalesce((select a.attname from pg_attribute a where a.attrelid = c.oid and a.attnum = o.conkey[1] and a.attisdropped = false),'') as target_colname1,
coalesce((select a.attname from pg_attribute a where a.attrelid = c.oid and a.attnum = o.conkey[2] and a.attisdropped = false),'') as target_colname2,
coalesce((select a.attname from pg_attribute a where a.attrelid = c.oid and a.attnum = o.conkey[3] and a.attisdropped = false),'') as target_colname3
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) IN (2,3)),
index_with_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,
coalesce((select a.attname from pg_attribute a where a.attrelid = ct.oid and a.attnum = i.indkey[0] and a.attisdropped = false),'') as column_name1,
coalesce((select a.attname from pg_attribute a where a.attrelid = ct.oid and a.attnum = i.indkey[1] and a.attisdropped = false),'') as column_name2,
coalesce((select a.attname from pg_attribute a where a.attrelid = ct.oid and a.attnum = i.indkey[2] and a.attisdropped = false),'') as column_name3
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 IN (2,3) 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))
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
CASE WHEN (lower(column_name1) IN ('id','code') OR lower(column_name1)=lower(table_name))
OR (lower(column_name2) IN ('id','code') OR lower(column_name2)=lower(table_name))
OR (lower(column_name3) IN ('id','code') OR lower(column_name3)=lower(table_name))
THEN
replace(replace(replace(replace(lower(constraint_name), lower(table_name),'<b>table</b>'), lower(column_name1), '<b>column1</b>'), lower(column_name2), '<b>column2</b>'), lower(column_name3), '<b>column3</b>')
ELSE
replace(replace(replace(replace(lower(constraint_name), lower(column_name1),'<b>column1</b>'), lower(column_name2),'<b>column2</b>'), lower(column_name3),'<b>column3</b>'), lower(table_name), '<b>table</b>') END AS pattern, type, constraint_name, table_name || ':' || column_name1 || ', ' || column_name2 || CASE WHEN column_name3<>'' THEN ', ' || column_name3 ELSE '' END AS pattern_source
FROM key_constraint_names
UNION ALL SELECT replace(replace(replace(replace(lower(constraint_name), lower(column_name1),'<b>column1</b>'), lower(column_name2),'<b>column2</b>'), lower(column_name3),'<b>column3</b>'), lower(table_name), '<b>table</b>') AS pattern, type, constraint_name,
case when column_name1='' then table_name else table_name || ':' || column_name1 || ', ' || column_name2 || CASE WHEN column_name3<>'' THEN ', ' || column_name3 ELSE '' END end AS pattern_source
FROM exclude_constraint_names
UNION ALL SELECT replace(replace(replace(replace(lower(index_name), lower(column_name1),'<b>column1</b>'), lower(column_name2),'<b>column2</b>'), lower(column_name3),'<b>column3</b>'), lower(table_name), '<b>table</b>') AS pattern, type, index_name, table_name || ':' || column_name1 || ', ' || column_name2 || CASE WHEN column_name3<>'' THEN ', ' || column_name3 ELSE '' END AS pattern_source
FROM index_with_col_name
UNION ALL SELECT replace(replace(replace(replace(lower(constraint_name), lower(column_name1),'<b>column1</b>'), lower(column_name2),'<b>column2</b>'), lower(column_name3),'<b>column3</b>'), lower(table_name), '<b>table</b>') AS pattern, type, constraint_name, table_name || ':' || column_name1 || ', ' || column_name2 || CASE WHEN column_name3<>'' THEN ', ' || column_name3 ELSE '' END AS pattern_source
FROM chk_constraint_names
UNION ALL SELECT
CASE
WHEN (foreign_table ILIKE '%' || target_table || '%' AND foreign_table<>target_table) THEN
replace(replace(replace(replace(replace(lower(conname), lower(target_colname1),'<b>column1</b>'), lower(target_colname2),'<b>column2</b>'), lower(target_colname3),'<b>column3</b>'), lower(foreign_table), '<b>foreign_table</b>'), lower(target_table), '<b>table</b>')
ELSE
replace(replace(replace(replace(replace(lower(conname), lower(target_colname1),'<b>column1</b>'), lower(target_colname2),'<b>column2</b>'), lower(target_colname3),'<b>column3</b>'), lower(target_table), '<b>table</b>'), lower(foreign_table), '<b>foreign_table</b>')
END AS pattern, type, conname,
'primary: ' || foreign_table || ':' || foreign_colname1 || ', ' || foreign_colname2 || CASE WHEN foreign_colname3<>'' THEN ', ' || foreign_colname3 ELSE '' END ||
' child: ' || target_table || ':' || target_colname1 || ', ' || target_colname2 || CASE WHEN target_colname3<>'' THEN ', ' || target_colname3 ELSE '' END AS pattern_source
FROM fk_constraint_names
) AS foo
GROUP BY pattern
ORDER BY types, Count(*) DESC, pattern;