Timezone-aware logic on timezone-unaware columns

Copy SQL

WITH all_simple_checks AS (SELECT
  (SELECT nspname FROM pg_namespace WHERE oid=c.relnamespace) AS object_schema,
  c.relname || '.' || a.attname AS target,  
  COALESCE(basetype.typname, t.typname) AS data_type,
  o.conname AS constraint_name,  
  pg_get_constraintdef(o.oid) AS check_clause,
  'TABLE CHECK' AS check_type
FROM pg_constraint o
INNER JOIN pg_class c ON c.oid = o.conrelid
INNER JOIN pg_attribute a ON a.attrelid = o.conrelid AND a.attnum = o.conkey[1]
INNER JOIN pg_type t ON a.atttypid = t.oid
LEFT JOIN pg_type basetype ON t.typtype = 'd' AND t.typbasetype = basetype.oid
WHERE o.contype = 'c' AND cardinality(o.conkey) = 1
UNION SELECT
  ns.nspname AS object_schema,
  t.typname AS target,  
  basetype.typname AS data_type,
  c.conname AS constraint_name,
  pg_get_constraintdef(c.oid) AS check_clause,
  'DOMAIN CHECK' AS check_type
FROM pg_constraint c
INNER JOIN pg_type t ON c.contypid = t.oid
INNER JOIN pg_namespace ns ON t.typnamespace = ns.oid
INNER JOIN pg_type basetype ON t.typbasetype = basetype.oid
WHERE
  c.contype = 'c'
  AND c.contypid <> 0
  AND pg_get_constraintdef(c.oid) <> 'CHECK ((VALUE IS NOT NULL))')
SELECT object_schema, target, check_clause, check_type, constraint_name, data_type
FROM all_simple_checks
WHERE  data_type IN ('date', 'timestamp')
AND check_clause ~ '::timestamp with time zone'
ORDER BY check_type, object_schema, target;