At most one row is permitted in a table (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 
n.nspname 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
inner join pg_namespace n on c.relnamespace=n.oid
WHERE cardinality(o.conkey)=1 
AND o.contype = 'c' 
AND c.relkind IN ('r','f')
AND n.nspname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
UNION SELECT table_schema, table_name, table_type, column_name, data_type, check_clause, constraint_name
FROM columns_with_domains_chk),

one_value AS (SELECT  table_schema, table_name, table_type,column_name, type_name, constraint_name, check_clause, is_nullable
FROM simple_chk AS ch INNER JOIN INFORMATION_SCHEMA.columns AS c USING(table_schema, table_name, column_name)
WHERE check_clause~'[[:space:]](=)[[:space:]]' 
AND check_clause!~*'[[:space:]]any[[:space:]][\(]+array\[' 
AND check_clause!~*'(<|>|<>|>=|<=)' 
AND check_clause!~*'(left|right|substring)' 
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)')),

simple_keys as (select 
o.conname as constraint_name,
(select nspname from pg_namespace where oid=c.relnamespace) as table_schema,
c.relname as table_name, 
a.attname AS column_name,
CASE WHEN c.relkind ='r' THEN 'BASE TABLE' ELSE 'FOREIGN TABLE' END AS table_type
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
where o.contype in ('p', 'u')
and c.relkind in ('r','f')
and cardinality(o.conkey)=1)

SELECT table_schema, table_name, s.table_type, s.constraint_name AS key_constraint_name, column_name AS key_colmn_name, o.constraint_name AS check_constraint_name, o.check_clause
FROM simple_keys AS s INNER JOIN one_value AS o USING (table_schema, table_name, column_name)
WHERE o.is_nullable='NO'
ORDER BY table_schema, table_name;