Goal This query identifies base tables containing two or more DATE or TIMESTAMP (with or without time zone) columns where at least one temporal column is not compared with other temporal columns in a purely temporal multi-column CHECK constraint. Rather than checking only for the complete absence of constraints, it catches partial coverage—for example, if a table defines created_at, updated_at, and archived_at, having CHECK (updated_at >= created_at) still leaves archived_at uncovered and flagged for review. Multiple temporal columns in a table typically represent sequential events or lifecycle milestones that must obey a chronological order. Note that only multi-column CHECK constraints composed exclusively of DATE or TIMESTAMP columns count as valid coverage (constraints mixing non-temporal columns are excluded). The query outputs all temporal columns in each flagged table alongside the uncovered columns and their count.
Type Problem detection Each row in the result could represent a flaw in the design
Reliability Medium Medium number of false-positive results
License MIT (opens in new tab)
Data Source INFORMATION_SCHEMA+system catalog
SQL Query
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)),

-- LISATUD: Koondame siia ka kõigi tabeli date/timestamp veergude nimekirja
multiple_temporal_cols AS (
    SELECT 
        table_schema, 
        table_name,
        string_agg(column_name || '(' || data_type || ')', ';<br>' ORDER BY column_name) AS all_temporal_columns
    FROM temporal_cols
    GROUP BY table_schema, table_name
    HAVING Count(*) > 1
)

SELECT 
    b.table_schema, 
    b.table_name, 
    m.all_temporal_columns,
    string_agg(b.column_name || '(' || b.data_type || ')', ';<br>' ORDER BY b.column_name) AS not_covered_columns, 
    Count(*) AS nr_of_not_covered_cols
FROM temporal_cols AS b
INNER JOIN multiple_temporal_cols AS m 
    ON b.table_schema = m.table_schema
   AND b.table_name = m.table_name
WHERE 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 b.table_schema, b.table_name, m.all_temporal_columns
ORDER BY b.table_schema, b.table_name, Count(*) DESC;

Fixing Suggestion

Compare not_covered_columns against all_temporal_columns. Add a multi-column CHECK constraint (e.g., CHECK (archived_at >= created_at)) for any temporal column that must follow a chronological sequence, or confirm that the uncovered column is chronologically independent.

Collections

This query belongs to the following collections:

Find problems about integrity constraints

A selection of queries that return information about the state of integrity constraints in the datadabase. Contains all the types of queries - problem detection, software measure, and general overview

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 .

NameDescription
Find problems about integrity constraintsA selection of queries that return information about the state of integrity constraints in the datadabase. Contains all the types of queries - problem detection, software measure, and general overview
Find problems automaticallyQueries, 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:

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).

NameDescription
CHECK constraintsQueries of this category provide information about CHECK constraints.
Temporal dataQueries of this category provide information about temporal (time-related) data that is kept in the database.
Validity and completenessQueries 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).