CHECK constraints on columns with temporal data

Copy SQL

WITH checks AS (SELECT ccu.table_schema, ccu.table_name, t.table_type, ccu.column_name, cc.check_clause, cc.constraint_name, 'TABLE CHECK' AS check_type, c.data_type
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)
INNER JOIN INFORMATION_SCHEMA.columns c USING (table_schema, table_name, column_name)
WHERE  c.data_type IN ('date', 'timestamp without time zone', 'timestamp with time zone', 'time without time zone', 'time with time zone') AND
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)
UNION SELECT cdu.table_schema, cdu.table_name, t.table_type, cdu.column_name, cc.check_clause, cc.constraint_name, 'DOMAIN CHECK' AS check_type, c.data_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)
INNER JOIN information_schema.columns c USING (table_schema, table_name, column_name)
WHERE c.data_type IN ('date', 'timestamp without time zone', 'timestamp with time zone', 'time without time zone', 'time with time zone') AND
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)),
temp_columns AS (SELECT c.table_schema, c.table_name, t.table_type, c.column_name, c.data_type, '' AS check_clause, '' AS check_type
FROM information_schema.tables t INNER JOIN information_schema.schemata s ON t.table_schema=s.schema_name
INNER JOIN information_schema.columns c USING (table_schema, table_name)
WHERE c.data_type IN ('date', 'timestamp without time zone', 'timestamp with time zone', 'time without time zone', 'time with time zone') 
AND table_type IN ('BASE TABLE','FOREIGN')
AND (T.table_schema = 'public' OR S.schema_owner<>'postgres')),
result AS (SELECT table_schema, table_name, table_type, column_name, data_type, check_clause, check_type
FROM checks
UNION SELECT table_schema, table_name, table_type, column_name, data_type, check_clause, check_type
FROM temp_columns AS tc
WHERE NOT EXISTS (SELECT 1
FROM checks AS ch
WHERE ch.table_schema=tc.table_schema AND ch.table_name=tc.table_name AND ch.column_name=tc.column_name))
SELECT table_schema, table_name, table_type, check_clause, check_type, string_agg(column_name || '.' || data_type, ',<br>' ORDER BY column_name) AS columns
FROM result
GROUP BY table_schema, table_name, table_type, check_clause, check_type
ORDER BY table_schema, table_name, check_clause;