WITH checks AS(SELECT
n.nspname || '.' || c.relname AS container_name,
CASE WHEN c.relkind='r' THEN 'BASE TABLE'
WHEN c.relkind='v' THEN 'VIEW'
WHEN c.relkind='m' THEN 'MATERIALIZED VIEW'
WHEN c.relkind='f' THEN 'FOREIGN TABLE'
WHEN c.relkind='p' THEN 'PARTITIONED TABLE'
ELSE 'TABLE' END AS container_type,
conname AS constraint_name,
replace(pg_get_constraintdef(o.oid),'CHECK','') AS constraint_src
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='c'
UNION SELECT dc.domain_schema || '.' || dc.domain_name AS container_name, 
'DOMAIN' AS container_type,
cc.constraint_name, 
cc.check_clause
FROM INFORMATION_SCHEMA.domain_constraints AS dc
INNER JOIN INFORMATION_SCHEMA.domains AS d USING (domain_schema, domain_name)
INNER JOIN INFORMATION_SCHEMA.check_constraints AS cc USING (constraint_schema, constraint_name)
WHERE cc.check_clause NOT LIKE '%IS NOT NULL'  AND 
dc.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))
SELECT container_name, container_type, constraint_name, constraint_src,
CASE WHEN constraint_name~*'(oige|right|correct)' THEN 'Constraints cannot ensure correctness of data' 
ELSE 'Noise words like valid or follows rules are not needed in the name' END AS comment_about_the_name
FROM checks
WHERE constraint_name~*'(oige|korrektne|valiidne|reegliparane|vastab([ ]|_)*reeglitele|right|correct|valid|(corresponds|follows)([ ]|_)*rules)'
AND constraint_src!~*'(oige|korrektne|valiidne|reegliparane|vastab([ ]|_)*reeglitele|right|correct|valid|(corresponds|follows)([ ]|_)*rules)'
ORDER BY container_type, container_name;