WITH pkey_constraint_names AS (
select
'PRIMARY KEY' AS type,
o.conname AS constraint_name,
np.nspname as schema_name,
c.relname as table_name,
cardinality(o.conkey) as cardinality
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
INNER JOIN pg_namespace AS np ON c.relnamespace=np.oid
INNER JOIN pg_authid AS a ON np.nspowner=a.oid
WHERE (np.nspname='public' OR rolname<>'postgres')
and o.contype ='p' and cardinality(o.conkey)>1),
ukey_constraint_names AS (
select
'UNIQUE' AS type,
o.conname AS constraint_name,
np.nspname as schema_name,
c.relname as table_name,
cardinality(o.conkey) as cardinality
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
INNER JOIN pg_namespace AS np ON c.relnamespace=np.oid
INNER JOIN pg_authid AS a ON np.nspowner=a.oid
WHERE (np.nspname='public' OR rolname<>'postgres')
and o.contype='u' and cardinality(o.conkey)>1),
chk_constraint_names AS (select
'CHECK' AS type,
o.conname AS constraint_name,
np.nspname as schema_name,
c.relname as table_name,
cardinality(o.conkey) as cardinality
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
INNER JOIN pg_namespace AS np ON c.relnamespace=np.oid
INNER JOIN pg_authid AS a ON np.nspowner=a.oid
WHERE (np.nspname='public' OR 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,
np.nspname as schema_name,
c.relname as table_name,
cardinality(o.conkey) as cardinality
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
INNER JOIN pg_namespace AS np ON c.relnamespace=np.oid
INNER JOIN pg_authid AS a ON np.nspowner=a.oid
WHERE (np.nspname='public' OR rolname<>'postgres')
and o.contype = 'f' and cardinality(o.conkey)>1),
exclude_constraint_names AS (select
'EXCLUDE' AS type,
o.conname AS constraint_name,
np.nspname as schema_name,
c.relname as table_name,
cardinality(o.conkey) as cardinality
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
INNER JOIN pg_namespace AS np ON c.relnamespace=np.oid
INNER JOIN pg_authid AS a ON np.nspowner=a.oid
WHERE (np.nspname='public' OR rolname<>'postgres')
and o.contype = 'x' and cardinality(o.conkey)>1)
SELECT cardinality, type, schema_name, table_name, constraint_name
FROM pkey_constraint_names
UNION SELECT cardinality, type, schema_name, table_name, constraint_name
FROM ukey_constraint_names
UNION SELECT cardinality, type, schema_name, table_name, constraint_name
FROM chk_constraint_names
UNION SELECT cardinality, type, schema_name, table_name, constraint_name
FROM fk_constraint_names
UNION SELECT cardinality, type, schema_name, table_name, constraint_name
FROM exclude_constraint_names
ORDER BY type, cardinality DESC, schema_name, table_name, constraint_name;