CHECK constraints on TIMESTAMPTZ and TSTZRANGE columns that do not explicitly specify a time zone

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 ('timestamp with time zone', 'tstzrange') 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='timestamp 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))
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 checks
WHERE check_clause~'[[:digit:]]{4}'
AND (check_clause~'\+[[:digit:]]{2}' OR check_clause~'::timestamp without time zone')
AND check_clause!~*'AT TIME ZONE'
GROUP BY table_schema, table_name, table_type, check_clause, check_type
ORDER BY table_schema, table_name, check_clause;