How to validate data in PostgreSQL

A practical guide to validating data quality in PostgreSQL — the six checks every table needs, the SQL to write them, the null and locking traps to avoid, and how to turn them into a versioned data contract.

2026-08-27 · 7 min read

To validate data in PostgreSQL, write each expectation as an aggregate query that returns a violation count, run them together against the table, and fail when a count exceeds its threshold. Six checks cover the overwhelming majority of real incidents: nulls in required columns, duplicate keys, values outside an allowed set, numbers outside a plausible range, malformed strings, and stale rows. Postgres gives you real regular expressions and honest null semantics, which makes this easier than in most engines — but it has its own traps, mostly around NULL, statistics and long-running scans on a primary.

Why PostgreSQL is a good place to start

Postgres has the richest validation surface of the common warehouses:

• Real regex. The ~ operator is POSIX-extended, so anchored patterns, alternation and quantifiers all work without translation.
• information_schema plus pg_catalog. Column types, nullability and primary keys are queryable in a standard form, so schema import is exact rather than inferred.
• Cheap approximate counts. pg_class.reltuples gives a planner estimate for free when an exact COUNT(*) is too expensive.

The cost of that richness is that Postgres is usually your transactional primary, not a warehouse. Validation queries are full scans, and full scans on a primary compete with your application.

The six checks every table needs

1. Completeness — nulls in required columns

SELECT count(*) AS violations
FROM sales.orders
WHERE order_id IS NULL;

2. Uniqueness — duplicate business keys

SELECT count(*) AS violations
FROM (
    SELECT order_id
    FROM sales.orders
    WHERE order_id IS NOT NULL
    GROUP BY order_id
    HAVING count(*) > 1
) dupes;

A unique index would prevent this at write time. In an analytics schema loaded by an ELT job, the index usually does not exist — dbt models are recreated as plain tables, and CREATE TABLE AS SELECT carries no constraints. The duplicate check is the only thing standing in.

3. Conformity — values outside an allowed set

SELECT count(*) AS violations
FROM sales.orders
WHERE status IS NOT NULL
  AND status NOT IN ('pending', 'paid', 'shipped', 'refunded');

The IS NOT NULL guard is not optional. NULL NOT IN (...) is NULL, not true, so nulls disappear from the result and an all-null column reports perfect conformity. This is the single most common false pass in hand-written data quality SQL.

4. Accuracy — numbers outside a plausible range

SELECT count(*) AS violations
FROM sales.orders
WHERE total_amount IS NOT NULL
  AND (total_amount < 0 OR total_amount > 100000);

Use numeric for money. double precision bounds compare with rounding error, and a check written as > 100000 will occasionally disagree with itself.

5. Conformity — malformed identifiers

SELECT count(*) AS violations
FROM sales.orders
WHERE reference IS NOT NULL
  AND reference !~ '^ORD-[0-9]{6}
#39;;

Use ~ for case-sensitive matching and ~* for case-insensitive. Unlike SQL Server, no translation is needed — the pattern you write is the pattern that runs.

6. Timeliness — freshness

SELECT count(*) AS violations
FROM sales.orders
WHERE created_at < now() - interval '24 hours';

Prefer timestamptz over timestamp. A bare timestamp column has no zone, so now() compares against whatever the session's TimeZone happens to be, and the same check gives different answers from two clients.

Referential integrity across tables

Postgres is one of the few engines where the cross-table check is genuinely cheap, because the planner will hash-join rather than run a correlated subquery:

SELECT count(*) AS violations
FROM sales.orders o
LEFT JOIN sales.customers c ON o.customer_id = c.id
WHERE o.customer_id IS NOT NULL
  AND c.id IS NULL;

Orphaned foreign keys are the classic symptom of a partial backfill — one table reloaded, its parent not — and they are invisible until a dashboard quietly drops rows in an inner join.

Getting a safe read-only role

CREATE ROLE catalyst_ro LOGIN PASSWORD '<generated>';
GRANT CONNECT ON DATABASE analytics TO catalyst_ro;
GRANT USAGE ON SCHEMA sales TO catalyst_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO catalyst_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA sales
  GRANT SELECT ON TABLES TO catalyst_ro;

The ALTER DEFAULT PRIVILEGES line is the one everyone forgets: without it, every table your ELT job recreates tomorrow is invisible to the validation role, and checks start failing with permission errors that look like data problems.

Then protect the primary:

ALTER ROLE catalyst_ro SET statement_timeout = '60s';
ALTER ROLE catalyst_ro SET idle_in_transaction_session_timeout = '30s';

Better still, point the connection at a read replica. Validation queries are read-only aggregate scans with no ordering requirement, which is exactly the workload replicas exist for.

From ad-hoc SQL to a data contract

Ad-hoc scripts drift out of sync with the tables they check. Expressing the expectations declaratively fixes that — the rules live next to the schema, in a format that both a person and a runner can read. Catalyst uses the Open Data Contract Standard:

apiVersion: v3.0.0
kind: DataContract
info:
  title: orders
  version: 1.4.0
  owner: data-platform
schema:
  - name: orders
    physicalName: orders
    physicalType: table
    properties:
      - name: order_id
        logicalType: string
        physicalType: uuid
        required: true
        primaryKey: true
        quality:
          - rule: nullCount
            dimension: completeness
            severity: error
            mustBe: "0"
          - rule: duplicateCount
            dimension: uniqueness
            severity: error
            mustBe: "0"
      - name: customer_id
        logicalType: string
        quality:
          - rule: referentialIntegrity
            dimension: consistency
            severity: error
            mustBe: "customers.id"
      - name: status
        logicalType: string
        quality:
          - rule: validValues
            dimension: conformity
            severity: error
            mustBe: "['pending', 'paid', 'shipped', 'refunded']"
      - name: total_amount
        logicalType: number
        physicalType: numeric
        quality:
          - rule: between
            dimension: accuracy
            severity: error
            mustBe: "[0, 100000]"
      - name: created_at
        logicalType: timestamp
        physicalType: timestamptz
        quality:
          - rule: freshness
            dimension: timeliness
            severity: error
            mustBe: "<= 24h"
    quality:
      - rule: rowCount
        dimension: consistency
        severity: warning
        mustBe: "> 0"

Catalyst imports the columns from information_schema, proposes a baseline contract from the types and nullability it finds, compiles each quality entry to the Postgres SQL shown above, and records the result of every check. Because the YAML round-trips — comments and key order survive edits made in the visual builder — the contract you review in a pull request is byte-for-byte the contract that runs.

PostgreSQL gotchas worth knowing

Scheduling and alerting

Run the checks immediately after the load that produces the data, not on a round-number cron. A daily table validated at 06:00 when the ELT finishes at 06:40 fails every morning for a reason that has nothing to do with quality.

Alert on transitions rather than state. "Orders started failing" is actionable; "orders is still failing" repeated hourly is how a channel gets muted. Split severity accordingly — error for the checks that should block downstream consumers, warning for the ones you want as a trend line.

Frequently asked questions

Can I validate PostgreSQL data without writing SQL?

Yes. Declare the expectation — required, unique, allowed values, numeric range, regex pattern, maximum age, foreign key — and let the tool compile it. Catalyst reads your columns and types from information_schema, suggests a starting set of rules, and generates the queries. Hand-written SQL is reserved for genuinely bespoke logic via a customSql rule.

Does validation need write access to my database?

No. Every check is a SELECT returning one number. CONNECT on the database, USAGE on the schema and SELECT on the tables is the complete permission set. Catalyst stores only metadata and check results; the rows never leave your server.

Should I run validations against a read replica?

Yes, when you have one. The checks are full aggregate scans with no consistency requirement beyond "recent", so replica lag of a few seconds is irrelevant, and moving them off the primary removes the main operational objection to running them often.

How do I check a regular expression in Postgres?

Use the ~ operator for case-sensitive POSIX matching or ~* for case-insensitive, and negate with !~. Postgres needs no pattern translation, unlike SQL Server, where regex rules must be rewritten as LIKE.

How is this different from dbt tests or CHECK constraints?

CHECK constraints reject bad rows at write time, which is right for a transactional table and wrong for a warehouse where you would rather land the data and quarantine it. dbt tests run inside a dbt build, so they only cover models dbt owns. A data contract sits above both: it describes the table's guarantees in a portable format, versioned independently of any one pipeline tool, and applies equally to tables produced by dbt, Airflow or a hand-rolled loader.