Number of system-generated and user-defined constraint names by constraint type (constraints that involve more than one column)

Copy SQL

with const as (select 
o.conname AS constraint_name, 
nc.nspname as table_schema,
c.relname as table_name, 
c.oid as const_table_oid,
o.conkey AS table_col,
CASE WHEN o.contype='p' THEN 'PRIMARY KEY'
WHEN o.contype='u' THEN 'UNIQUE'
WHEN o.contype='c' THEN 'CHECK'
WHEN o.contype='f' THEN 'FOREIGN KEY'
WHEN o.contype='x' THEN 'EXCLUDE' END AS constraint_type
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','c','f','x')
and cardinality(o.conkey)>1),
const_unnest as (select constraint_name, table_schema, table_name, const_table_oid, table_col, const_col_num, ordin, constraint_type
from const, unnest(const.table_col) with ordinality as k(const_col_num, ordin)),
const_with_names as (select constraint_name, table_schema, table_name, constraint_type, string_agg(a_const.attname, '_' order by ordin) as const_col
from const_unnest k inner join pg_attribute a_const on k.const_col_num = a_const.attnum and k.const_table_oid = a_const.attrelid and a_const.attisdropped = false
group by constraint_name, table_schema, table_name, constraint_type),
stats AS (SELECT constraint_type, Count(*) AS cnt_total, 
Count(*) FILTER (WHERE lower(constraint_name) = lower(table_name) || '_pkey') AS cnt_system_generated
FROM const_with_names
WHERE constraint_type='PRIMARY KEY'
GROUP BY constraint_type

UNION SELECT constraint_type, Count(*) AS cnt_total, 
Count(*) FILTER (WHERE lower(constraint_name) = lower(table_name) || '_' || lower(const_col) || '_key') AS cnt_system_generated
FROM const_with_names
WHERE constraint_type='UNIQUE'
GROUP BY constraint_type

UNION SELECT constraint_type, Count(*) AS cnt_total, 
Count(*) FILTER (WHERE lower(constraint_name) = lower(table_name) || '_' || lower(const_col) || '_excl') AS cnt_system_generated
FROM const_with_names
WHERE constraint_type='EXCLUDE'
GROUP BY constraint_type

UNION SELECT constraint_type, Count(*) AS cnt_total, 
Count(*) FILTER (WHERE regexp_like(constraint_name, '^' || table_name || '_fkey[[:digit:]]*$','i')) AS cnt_system_generated
FROM const_with_names
WHERE constraint_type='FOREIGN KEY'
GROUP BY constraint_type

UNION SELECT constraint_type, Count(*) AS cnt_total, 
Count(*) FILTER (WHERE regexp_like(constraint_name, '^' || table_name || '_check[[:digit:]]*$','i')) AS cnt_system_generated
FROM const_with_names
WHERE constraint_type='CHECK'
GROUP BY constraint_type

)
SELECT constraint_type, cnt_total AS number_of_constraints, cnt_system_generated AS number_of_constraints_with_system_generated_names, cnt_total - cnt_system_generated AS number_of_constraints_with_user_defined_names
FROM stats
ORDER BY cnt_system_generated DESC, constraint_type;