WITH f AS (select (select nspname from pg_namespace where oid=f.relnamespace) as foreign_schema,
f.relname as foreign_table,
(select a.attname from pg_attribute a where a.attrelid = f.oid and a.attnum = o.confkey[1] and a.attisdropped = false) as foreign_colname,
(select nspname from pg_namespace where oid=c.relnamespace) as target_schema,
c.relname as target_table,
(select a.attname from pg_attribute a where a.attrelid = c.oid and a.attnum = o.conkey[1] and a.attisdropped = false) as target_colname 
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
inner join pg_class f on f.oid = o.confrelid
where o.contype = 'f' AND cardinality(o.conkey)=1),

c AS (SELECT table_schema, table_name, column_name, constraint_name, regexp_replace(check_clause, '([(| ])(' || column_name || ')([) | ])', E'\\1VALUE\\3')  AS check_clause, domain_schema, domain_name, 'TABLE CHECK' AS check_type
FROM (select 
o.conname as constraint_name,
(select nspname from pg_namespace where oid=c.relnamespace) as table_schema,
c.relname as table_name, 
(select a.attname from pg_attribute a where a.attrelid = c.oid and a.attnum = o.conkey[1] and a.attisdropped = false) as column_name,
substring(pg_get_constraintdef(o.oid),7) AS check_clause, NULL AS domain_schema, NULL AS domain_name
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
where o.contype = 'c'  AND cardinality(o.conkey)=1) AS chk
WHERE 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)
UNION SELECT cdu.table_schema, cdu.table_name, cdu.column_name, dc.constraint_name, cc.check_clause, cdu.domain_schema, cdu.domain_name, 'DOMAIN CHECK' AS check_type
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)), 

redundant_constraints AS (SELECT f.target_schema, f.target_table, cd.constraint_name, cd.check_type, cd.domain_schema AS domain_schema_target, cd.domain_name AS domain_name_target
FROM f, c AS cp, c AS cd
WHERE 
f.foreign_schema=cp.table_schema AND
f.foreign_table=cp.table_name AND
f.foreign_colname=cp.column_name AND
f.target_schema = cd.table_schema AND
f.target_table = cd.table_name AND
f.target_colname = cd.column_name AND
cd.check_clause=cp.check_clause 
AND ((cd.domain_name IS DISTINCT FROM cp.domain_name) OR (cd.domain_name IS NULL AND cp.domain_name IS NULL)))

SELECT DISTINCT CASE WHEN check_type='TABLE CHECK' THEN format('ALTER TABLE %1$I.%2$I DROP CONSTRAINT %3$I;', target_schema, target_table, constraint_name) 
WHEN check_type='DOMAIN CHECK' THEN format('ALTER DOMAIN %1$I.%2$I DROP CONSTRAINT %3$I;', domain_schema_target, domain_name_target, constraint_name) END AS statements
FROM redundant_constraints
ORDER BY statements;