WITH names AS (
SELECT n.nspname AS schema,
conname AS suspected_name,
c.relname AS container,
'TABLE' AS container_type,
CASE WHEN o.contype='p' THEN 'PRIMARY KEY'
WHEN o.contype='u' THEN 'UNIQUE'
WHEN o.contype='f' THEN 'FOREIGN KEY'
WHEN o.contype='c' THEN 'TABLE CHECK'
WHEN o.contype='x' THEN 'EXCLUDE' END AS type
FROM pg_constraint o INNER JOIN pg_class c ON o.conrelid=c.oid
INNER JOIN pg_namespace AS n ON n.oid=c.relnamespace
INNER JOIN pg_authid AS a ON n.nspowner=a.oid
WHERE (nspname='public' OR rolname<>'postgres')
AND o.contype IN ('p' ,'u','f','c','x')
AND conname~'_[[:alpha:]]{1,6}[0-9]*$'

UNION SELECT 
domain_schema,
constraint_name, 
domain_name,
'DOMAIN' AS container_type,
'DOMAIN CHECK' AS type
FROM INFORMATION_SCHEMA.domain_constraints
WHERE domain_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
AND constraint_name~'_[[:alpha:]]{1,6}[0-9]*$'

UNION SELECT n.nspname, c.relname, n.nspname, 'SCHEMA' AS container_type, 'INDEX' AS type
FROM pg_class c INNER JOIN pg_namespace n ON c.relnamespace=n.oid
INNER JOIN pg_index i ON c.oid=i.indexrelid
INNER JOIN pg_authid AS a ON n.nspowner=a.oid
WHERE (nspname='public' OR rolname<>'postgres') 
AND c.relkind = 'i' 
AND c.relname~'_[[:alpha:]]{1,6}[0-9]*$'
AND i.indisunique=false),

suffixes AS (SELECT schema, suspected_name, container, container_type, lower(regexp_replace(suspected_name,'.*_([[:alpha:]]{1,6})[0-9]*$','\1')) AS suffix, type
FROM names)

SELECT schema, container, container_type, suspected_name, suffix AS suspected_suffix, type
FROM suffixes
WHERE (type='REFERENTIAL' AND suffix IN ('pkey', 'pk','ak','uq','unique','idx','idxfk','chk','ck','check','excl')) OR
(type IN ('TABLE CHECK','DOMAIN CHECK') AND suffix IN ('pkey','pk', 'ak','uq','unique','fk', 'fkey','idx','idxfk','excl')) OR
(type='PRIMARY KEY' AND suffix IN ('ak','uq','unique','fk','fkey','idx','idxfk','chk','ck','check','excl')) OR
(type='UNIQUE' AND suffix IN ('pkey','pk','fk','fkey','idx','idxfk','chk','check','ck','excl')) OR
(type='EXCLUDE' AND suffix IN ('pkey','pk','ak','uq','unique','fk','fkey','idx','idxfk','chk','check','ck')) OR
(type='INDEX' AND suffix IN ('pkey','pk','ak','uq','unique','fk','fkey','chk','check','ck'))
ORDER BY type, schema, suffix;