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);