Redundant CHECK constraints (logical subsumption or equivalence) (empty strings and strings that consist of whitespace characters)

Copy SQL

WITH checks 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,
regexp_replace(pg_get_constraintdef(o.oid),'CHECK ', '','g') AS check_clause,
'TABLE CHECK' AS check_type, 
o.conname AS constraint_name
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
AND c.relkind IN ('r', 'f')
UNION ALL SELECT cdu.table_schema, cdu.table_name, t.table_type, cdu.column_name, regexp_replace(cc.check_clause, 'VALUE', '(' || cdu.column_name ||')::' || lower(d.data_type)) AS check_clause, 'DOMAIN CHECK' AS check_type, 
d.domain_schema||'.'||d.domain_name || '.' || cc.constraint_name AS 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.domains AS d 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))
SELECT table_schema, table_name, column_name, string_agg (check_clause, ';<br>') AS duplicate_constraints
FROM checks
WHERE check_clause~'[[:space:]]<>[[:space:]]+''[ ]*'''
OR (check_clause~'[[:space:]]~[*]{0,1}[[:space:]]+''[\^]{0,1}[^\^ '']+[\$]{0,1}'''
AND check_clause!~'(space|\\s|\.\*)')
OR check_clause~'[[:space:]]~[*]{0,1}[[:space:]]+''\[\^\\s\]'''
OR check_clause~'[[:space:]]~[*]{0,1}[[:space:]]+''\[\^\[\:space\:\]\]'''
OR check_clause~'[[:space:]]~[*]{0,1}[[:space:]]+''\\S'''
OR check_clause~'[[:space:]]!~[*]{0,1}[[:space:]]+''[\^][[]{2}[:]space[:][]]{2}[*+]{0,1}[$]'''
OR check_clause~'[[:space:]]!~[*]{0,1}[[:space:]]+''[\^]\\s[*+]{0,1}[$]'
OR check_clause~*'(length[[:space:]]*\(.+>[[:space:]]*0|0[[:space:]]*<.+length[[:space:]]*\()'
OR check_clause~*'(position[[:space:]]*\(.+>|<.+position[[:space:]]*\()'
GROUP BY table_schema, table_name, column_name
HAVING Count(*)>1
ORDER BY table_schema, table_name, column_name;