WITH chk AS (SELECT 
o.conname AS constraint_name,
n.nspname AS target_schema,
c.relname AS target_table, 
c.oid AS target_table_oid,
o.conkey AS target_col
FROM pg_constraint o INNER JOIN pg_class c ON c.oid = o.conrelid
INNER JOIN pg_namespace n ON c.relnamespace=n.oid
WHERE o.contype = 'c'  
AND c.relkind = 'r'
AND cardinality(o.conkey)>1),

chk_unnest AS (SELECT target_schema, target_table, target_table_oid, target_col, target_col_num, constraint_name
FROM chk, unnest(chk.target_col) WITH ORDINALITY AS f(target_col_num, ordin)),

chk_with_names AS (SELECT target_schema, target_table, a_target.attname AS target_col,
CASE WHEN t.typtype='d' THEN 
CASE WHEN bt.typcategory='A' THEN translate(bt.typname,'_','') || '(ARRAY)' ELSE bt.typname END
ELSE 
CASE WHEN t.typcategory='A' THEN translate(t.typname,'_','') || '(ARRAY)' 
WHEN t.typcategory='C' THEN t.typname || '(USER-DEFINED)'
ELSE t.typname END
END AS data_type,
constraint_name
FROM chk_unnest chk INNER JOIN  pg_attribute a_target ON chk.target_col_num = a_target.attnum AND chk.target_table_oid = a_target.attrelid AND a_target.attisdropped = false
INNER JOIN pg_type AS t ON a_target.atttypid=t.oid
LEFT JOIN pg_type AS bt ON t.typbasetype=bt.oid),

chk_temporal AS (SELECT target_schema, target_table, target_col
FROM chk_with_names
WHERE data_type~*'(date|timestamp)'
AND NOT EXISTS (SELECT *
FROM chk_with_names AS cwn
WHERE chk_with_names.target_schema=cwn.target_schema
AND chk_with_names.target_table=cwn.target_table
AND chk_with_names.constraint_name=cwn.constraint_name
AND cwn.data_type!~*'(date|timestamp)')),

temporal_cols AS (SELECT table_schema, table_name, column_name, data_type
FROM INFORMATION_SCHEMA.columns INNER JOIN INFORMATION_SCHEMA.tables USING (table_schema, table_name)
WHERE (data_type~*'(date|timestamp)')
AND table_type='BASE TABLE'
AND 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)),

multiple_temporal_cols AS (SELECT table_schema, table_name
FROM temporal_cols
GROUP BY table_schema, table_name
HAVING Count(*)>1)

SELECT table_schema, table_name, string_agg(column_name || '(' || data_type || ')', ';<br>' ORDER BY column_name) AS not_covered_columns, Count(*) AS nr_of_not_covered_cols
FROM temporal_cols AS b
WHERE EXISTS (SELECT *
FROM multiple_temporal_cols AS m
WHERE b.table_schema=m.table_schema
AND b.table_name=m.table_name)
AND NOT EXISTS (SELECT *
FROM chk_temporal AS ch
WHERE b.table_schema=ch.target_schema
AND b.table_name=ch.target_table
AND b.column_name=ch.target_col)
GROUP BY table_schema, table_name
ORDER BY table_schema, table_name, Count(*) DESC;