Only one value permitted in a base table or a foreign table column (based on check constraints)

Copy SQL

WITH columns_with_domains_chk AS (
SELECT cdu.table_schema, cdu.table_name, t.table_type, cdu.column_name, (SELECT data_type FROM INFORMATION_SCHEMA.domains AS d
WHERE d.domain_schema=dc.domain_schema AND d.domain_name=dc.domain_name) AS data_type, cc.check_clause, cc.constraint_name
FROM INFORMATION_SCHEMA.column_domain_usage AS cdu INNER JOIN INFORMATION_SCHEMA.tables AS t USING (table_schema, table_name)
INNER JOIN INFORMATION_SCHEMA.domain_constraints AS dc USING (domain_schema, domain_name)
INNER JOIN INFORMATION_SCHEMA.check_constraints AS cc USING (constraint_schema, constraint_name)
WHERE t.table_type IN ('BASE TABLE', 'FOREIGN') 
AND cc.check_clause NOT LIKE '%IS NOT NULL'    
AND cdu.table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)),

simple_chk AS (SELECT 
(SELECT nspname FROM pg_namespace WHERE oid=c.relnamespace) AS table_schema,
c.relname as table_name, 
CASE WHEN c.relkind='r' THEN 'BASE TABLE' ELSE 'FOREIGN' END AS table_type,
a.attname AS column_name,
t.typname AS type_name,
substring(pg_get_constraintdef(o.oid),7) AS check_clause,
o.conname AS constraint_name
FROM pg_constraint o INNER JOIN pg_class c ON c.oid = o.conrelid
INNER JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = o.conkey[1] AND a.attisdropped = FALSE
INNER JOIN pg_type t ON a.atttypid=t.oid
WHERE cardinality(o.conkey)=1 
AND o.contype = 'c' 
AND c.relkind IN ('r','f')
UNION SELECT table_schema, table_name, table_type, column_name, data_type, check_clause, constraint_name
FROM columns_with_domains_chk),

inheritance AS (SELECT pn.nspname AS parent_schema, p.relname AS parent_table, pc.nspname AS child_schema,  c.relname AS child_table,
c.relispartition AS child_is_partition
FROM pg_inherits pi INNER JOIN pg_class p ON pi.inhparent=p.oid
INNER JOIN pg_namespace pn ON p.relnamespace=pn.oid
INNER JOIN pg_authid AS ap ON pn.nspowner=ap.oid
INNER JOIN pg_class c ON pi.inhrelid=c.oid
INNER JOIN pg_namespace pc ON c.relnamespace=pc.oid
INNER JOIN pg_authid AS ac ON pc.nspowner=ac.oid
WHERE (pn.nspname='public' OR ap.rolname<>'postgres') 
AND (pc.nspname='public' OR ac.rolname<>'postgres'))

SELECT  table_schema, table_name, table_type, column_name, type_name, constraint_name, check_clause, is_nullable, i.parent_schema AS supertable_schema, i.parent_table AS supertable_name
FROM simple_chk AS ch INNER JOIN INFORMATION_SCHEMA.columns AS c USING(table_schema, table_name, column_name)
LEFT JOIN inheritance AS i ON c.table_schema=i.child_schema AND c.table_name=i.child_table
WHERE check_clause~'[[:space:]](=)[[:space:]]' 
AND check_clause!~*'[[:space:]]any[[:space:]][\(]+array\[' 
AND check_clause!~*'(<|>|<>|>=|<=)' 
AND check_clause!~*'(left|right|substring|trim|length)' 
AND check_clause!~*'[[:space:]](and|or)[[:space:]]' 
AND check_clause !~* ( '([[:alnum:]]|_)+[(]+' || column_name || '[)]+')
AND check_clause !~* ( '([[:alnum:]]|_)+[(]+value[)]+')
AND check_clause !~* ( '(current_date|current_timestamp|localtimestamp|now()|clock_timestamp|statement_timestamp|transaction_timestamp)')
ORDER BY table_schema, table_name, column_name;