WITH checks AS (SELECT ccu.table_schema, ccu.table_name, t.table_type, cc.check_clause, cc.constraint_name
FROM INFORMATION_SCHEMA.constraint_column_usage AS ccu INNER JOIN INFORMATION_SCHEMA.check_constraints AS cc USING (constraint_schema, constraint_name)
INNER JOIN INFORMATION_SCHEMA.tables AS t USING (table_schema, table_name)
WHERE cc.check_clause NOT LIKE '%IS NOT NULL' AND 
ccu.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 format('ALTER %4$s TABLE %1$I.%2$I VALIDATE CONSTRAINT %3$I;', table_schema, table_name, constraint_name, CASE WHEN table_type='FOREIGN' THEN table_type END) AS statements
FROM checks
WHERE check_clause~*'[[:space:]]not[[:space:]]+valid'
ORDER BY table_schema, table_name, constraint_name;

WITH checks AS (SELECT cdu.domain_schema, cdu.domain_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_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 format('ALTER DOMAIN %1$I.%2$I VALIDATE CONSTRAINT %3$I;', domain_schema, domain_name, constraint_name) AS statements
FROM checks
WHERE check_clause~*'[[:space:]]not[[:space:]]+valid'
ORDER BY domain_schema, domain_name, constraint_name;