Table constraints with the cardinality bigger than one

Copy SQL

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;