The number of table constraints with the cardinality bigger than one

Copy SQL

WITH pkey_constraint_names AS (
select 
o.oid,
'PRIMARY KEY' AS type,
cardinality(o.conkey) as cardinality
from pg_constraint o INNER JOIN pg_class AS c ON o.conrelid=c.oid
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 
o.oid,
'UNIQUE' AS type,
cardinality(o.conkey) as cardinality
from pg_constraint o INNER JOIN pg_class AS c ON o.conrelid=c.oid
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 
o.oid,
'CHECK' AS type,
cardinality(o.conkey) as cardinality
from pg_constraint o INNER JOIN pg_class AS c ON o.conrelid=c.oid
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 
o.oid,
'FOREIGN KEY' AS type,
cardinality(o.conkey) as cardinality
from pg_constraint o INNER JOIN pg_class AS c ON o.conrelid=c.oid
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 
o.oid,
'EXCLUDE' AS type,
cardinality(o.conkey) as cardinality
from pg_constraint o INNER JOIN pg_class AS c ON o.conrelid=c.oid
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),

constr AS (SELECT oid, cardinality, type
FROM pkey_constraint_names
UNION SELECT oid, cardinality, type
FROM ukey_constraint_names
UNION SELECT oid, cardinality, type
FROM chk_constraint_names
UNION SELECT oid, cardinality, type
FROM fk_constraint_names
UNION SELECT oid, cardinality, type
FROM exclude_constraint_names)

SELECT cardinality, type AS constraint_type, Count(*) AS number_of_constraints
FROM constr
GROUP BY CUBE (cardinality, type);