Copy SQL

WITH simple_check_constraints AS (
select 
n.nspname as table_schema,
c.relname as table_name,
a.attname as column_name,
o.conname as constraint_name,
substring(pg_get_constraintdef(o.oid)::text, 9,length(pg_get_constraintdef(o.oid))-10) AS check_clause
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
inner join pg_namespace n on o.connamespace=n.oid
INNER JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = o.conkey[1] AND a.attisdropped = FALSE
where o.contype = 'c' AND cardinality(o.conkey)=1),

fk as (select 
o.conname,
(select nspname from pg_namespace where oid=m.relnamespace) as key_schema,
m.relname as key_table, 
m.oid as key_table_oid,
o.conkey AS key_col
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
inner join pg_class m on m.oid = o.conrelid
where o.contype='f'  and o.conrelid in (select oid from pg_class c where c.relkind = 'r')),
fk_unnest as (select conname, key_schema, key_table, key_table_oid, key_col, key_col_num, ordin
from fk, unnest(fk.key_col) with ordinality as k(key_col_num, ordin)),
fk_with_names as (select conname, key_schema, key_table, a_key.attname as key_col
from fk_unnest k inner join pg_attribute a_key on k.key_col_num = a_key.attnum and k.key_table_oid = a_key.attrelid and a_key.attisdropped = false),

chk_constraints AS (SELECT table_schema, table_name, column_name, check_clause, constraint_name
FROM simple_check_constraints
UNION SELECT cdu.table_schema, cdu.table_name, cdu.column_name, 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_catalog, constraint_schema, constraint_name)
WHERE t.table_type='BASE TABLE' 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)),

chk_all AS (
SELECT table_schema, table_name, column_name, check_clause, constraint_name
FROM chk_constraints
UNION SELECT table_schema, table_name, column_name, 'No check' AS check_clause, '' AS constraint_name
FROM INFORMATION_SCHEMA.columns AS c INNER JOIN INFORMATION_SCHEMA.tables AS t USING (table_schema, table_name)
WHERE table_type='BASE TABLE'
AND 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)
AND NOT EXISTS (SELECT *
FROM chk_constraints AS scc
WHERE scc.table_schema=c.table_schema
AND scc.table_name=c.table_name
AND scc.column_name=c.column_name)),

chk_all_processed AS (SELECT table_schema, table_name, column_name, regexp_replace(check_clause, '^\((.+)\)$', '\1') AS check_clause, constraint_name
FROM chk_all),

chk_constraints_generalized AS (SELECT table_schema, table_name, column_name, constraint_name,
replace(lower(check_clause), lower (column_name), 'value') AS check_clause
FROM chk_all_processed AS c
WHERE NOT EXISTS (SELECT *
FROM fk_with_names AS f
WHERE f.key_schema=c.table_schema
AND f.key_table=c.table_name
AND f.key_col=c.column_name)),

chk_constraints_agg AS (SELECT table_schema, table_name, column_name, 
string_agg(check_clause,';' ORDER BY check_clause) AS check_clause
FROM chk_constraints_generalized
GROUP BY table_schema, table_name, column_name)

SELECT column_name, Count(DISTINCT check_clause) AS cnt_different,
Count(check_clause) AS cnt_total,
string_agg(DISTINCT check_clause, ';
' ORDER BY check_clause) AS consrc_different,
string_agg('' || table_schema || '.' || table_name || ' ' || check_clause, ';
' ORDER BY check_clause) AS consrc_all
FROM chk_constraints_agg AS cca
WHERE EXISTS (SELECT *
FROM chk_constraints_generalized AS ccg
WHERE ccg.column_name=cca.column_name
AND ccg.check_clause ILIKE '%No check%')
AND EXISTS (SELECT *
FROM chk_constraints_generalized AS ccg
WHERE ccg.column_name=cca.column_name
AND ccg.check_clause NOT ILIKE '%No check%')
GROUP BY column_name
ORDER BY Count(DISTINCT check_clause) DESC;