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)
SELECT schema_name, table_name, column_name, type, constraint_name
FROM pkey_constraint_names
WHERE lower(constraint_name) = lower(table_name) || '_pkey'
UNION SELECT schema_name, table_name, column_name, type, constraint_name
FROM ukey_constraint_names
WHERE lower(constraint_name) = lower(table_name) || '_' || lower(column_name) || '_key'
UNION SELECT schema_name, table_name, column_name, type, constraint_name
FROM chk_constraint_names
WHERE lower(constraint_name) = lower(table_name) || '_' || lower(column_name) || '_check'
UNION SELECT schema_name, table_name, column_name, type, constraint_name
FROM fk_constraint_names
WHERE lower(constraint_name) = lower(table_name) || '_' || lower(column_name) || '_fkey'
UNION SELECT schema_name, table_name, column_name, type, constraint_name
FROM exclude_constraint_names
WHERE lower(constraint_name) = lower(table_name) || '_' || lower(column_name) || '_excl'
ORDER BY schema_name, table_name, type, constraint_name;