Ensuring adjacent ranges in PostgreSQL

by

Summary

This incident examines a subtle but common data‑modeling failure: attempting to enforce temporal adjacency constraints in PostgreSQL using range types and exclusion constraints. While PostgreSQL 18’s WITHOUT OVERLAPS solves non‑overlapping ranges elegantly, enforcing gap‑free adjacency requires logic that exclusion constraints alone cannot guarantee.

Root Cause

The root cause is that PostgreSQL’s range operators and GiST exclusion constraints cannot express “must touch” semantics. The operator -|‑ enforces non‑adjacency, which is the opposite of what is needed. PostgreSQL simply does not provide a built‑in operator that enforces:

  • No overlaps
  • No gaps
  • Ranges must be exactly adjacent

This means the schema cannot guarantee adjacency without additional logic.

Why This Happens in Real Systems

Real systems frequently run into this because:

  • Temporal validity models often require continuous coverage.
  • Range types are optimized for overlap detection, not adjacency.
  • Exclusion constraints operate pairwise, but adjacency is a global property across all ranges for a given key.
  • Database constraints cannot easily express “the next row must start exactly when the previous row ends.”

Real-World Impact

When adjacency is not enforced:

  • Gaps appear, causing missing validity periods.
  • Downstream logic breaks, especially in billing, pricing, and regulatory systems.
  • Data becomes ambiguous, requiring expensive reconciliation.
  • Business rules drift, because the database cannot enforce the intended invariant.

Example or Code (if necessary and relevant)

Below is an example of how senior engineers typically enforce adjacency using a trigger, because constraints alone cannot express it:

CREATE OR REPLACE FUNCTION enforce_adjacent() RETURNS trigger AS $$
DECLARE
    prev_range daterange;
BEGIN
    SELECT valid
    INTO prev_range
    FROM whatever
    WHERE id = NEW.id
    ORDER BY upper(valid) DESC
    LIMIT 1;

    IF prev_range IS NOT NULL AND upper(prev_range)  lower(NEW.valid) THEN
        RAISE EXCEPTION 'Ranges must be adjacent for id %', NEW.id;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER enforce_adjacent_trigger
BEFORE INSERT ON whatever
FOR EACH ROW EXECUTE FUNCTION enforce_adjacent();

How Senior Engineers Fix It

Experienced engineers recognize that adjacency is a business rule, not a geometric property, and therefore implement it using:

  • Triggers that check the previous range’s upper bound.
  • Stored procedures that insert/update ranges atomically.
  • Upsert‑style APIs that compute the next valid range server‑side.
  • Materialized views or check functions to validate continuity periodically.
  • Application‑level logic that ensures correct sequencing before writing.

They avoid relying solely on exclusion constraints because those constraints cannot express adjacency.

Why Juniors Miss It

Junior engineers often miss this because:

  • They assume range operators cover all temporal logic, including adjacency.
  • They expect exclusion constraints to enforce multi‑row invariants, but adjacency requires global ordering.
  • They misunderstand that “no overlap” is not the same as “continuous coverage.”
  • They try to solve adjacency with schema‑only solutions, unaware that some invariants require procedural logic.

The key takeaway: adjacency is not a geometric constraint—it’s a sequencing rule, and PostgreSQL requires procedural enforcement for sequencing.

Leave a Comment