WITH pkey_constraint_names AS (
select
'PRIMARY KEY' AS type,
o.conname AS constraint_name,
n.nspname as schema_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 n on n.oid=c.relnamespace
inner join pg_authid AS a ON n.nspowner=a.oid
where (n.nspname='public' OR a.rolname<>'postgres')
and o.contype ='p' and cardinality(o.conkey)=1),
ukey_constraint_names AS (
select
'UNIQUE' AS type,
o.conname AS constraint_name,
n.nspname as schema_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 n on n.oid=c.relnamespace
inner join pg_authid AS a ON n.nspowner=a.oid
where (n.nspname='public' OR a.rolname<>'postgres')
and o.contype ='u' and cardinality(o.conkey)=1),
chk_constraint_names AS (select
'CHECK' AS type,
o.conname AS constraint_name,
(select nspname from pg_namespace where oid=c.relnamespace) as schema_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 n on n.oid=c.relnamespace
inner join pg_authid AS a ON n.nspowner=a.oid
where (n.nspname='public' OR a.rolname<>'postgres')
and o.contype ='c' and cardinality(o.conkey)=1),
fk_constraint_names AS (select
'FOREIGN KEY' AS type,
o.conname AS constraint_name,
n.nspname as schema_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 n on n.oid=c.relnamespace
inner join pg_authid AS a ON n.nspowner=a.oid
where (n.nspname='public' OR a.rolname<>'postgres')
and o.contype ='f' and cardinality(o.conkey)=1),
exclude_constraint_names AS (select
'EXCLUDE' AS type,
o.conname AS constraint_name,
n.nspname as schema_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 n on n.oid=c.relnamespace
inner join pg_authid AS a ON n.nspowner=a.oid
where (n.nspname='public' OR a.rolname<>'postgres')
and o.contype ='x' and cardinality(o.conkey)=1),
stats AS (SELECT type, Count(*) AS cnt_total,
Count(*) FILTER (WHERE lower(constraint_name) = lower(table_name) || '_pkey') AS cnt_system_generated
FROM pkey_constraint_names
GROUP BY type
UNION SELECT type, Count(*) AS cnt_total,
Count(*) FILTER (WHERE lower(constraint_name) = lower(table_name) || '_' || lower(column_name) || '_key') AS cnt_system_generated
FROM ukey_constraint_names
GROUP BY type
UNION SELECT type, Count(*) AS cnt_total,
Count(*) FILTER (WHERE regexp_like(constraint_name, '^' || table_name || '_' || column_name || '_check[[:digit:]]*$','i')) AS cnt_system_generated
FROM chk_constraint_names
GROUP BY type
UNION SELECT type, Count(*) AS cnt_total,
Count(*) FILTER (WHERE regexp_like(constraint_name, '^' || table_name || '_' || column_name || '_fkey[[:digit:]]*$','i')) AS cnt_system_generated
FROM fk_constraint_names
GROUP BY type
UNION SELECT type, Count(*) AS cnt_total,
Count(*) FILTER (WHERE lower(constraint_name) = lower(table_name) || '_' || lower(column_name) || '_excl') AS cnt_system_generated
FROM exclude_constraint_names
GROUP BY type
)
SELECT type AS 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;