This query identifies logically flawed CHECK constraints on columns with timezone-unaware data types (DATE and TIMESTAMP WITHOUT TIME ZONE). It specifically targets constraints that contain timezone-aware logic (e.g., using AT TIME ZONE). This represents a critical semantic mismatch: the data type stores a "naive" or "local" time with no timezone context, while the constraint attempts to evaluate it within a specific timezone. The outcome of such a check is non-deterministic, as it depends on the session's current TimeZone setting, leading to unpredictable and unreliable data validation. This is a design flaw; if timezone-aware logic is required, the column should use the TIMESTAMP WITH TIME ZONE (TIMESTAMPTZ) data type.
Type
Problem detection (Each row in the result could represent a flaw in the design)
Rewrite the CHECK constraint and remove references to time zones.
Data Source
system catalog only
SQL Query
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;
Collections
This query belongs to the following collections:
Name
Description
Find problems automatically
Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .
Categories
This query is classified under the following categories:
Name
Description
CHECK constraints
Queries of this category provide information about CHECK constraints.
Temporal data
Queries of this category provide information about temporal (time-related) data that is kept in the database.
Validity and completeness
Queries of this category provide information about whether database design represents the world (domain) correctly (validity) and whether database design captures all the information about the world (domain) that is correct and relevant (completeness).