PostgreSQL INT4RANGE: Enforce non-overlapping zones in SQL

#postgres #database #sql #webdev

Originally written by Omar Tarek, Software Developer at Stackdrop. Posted here with his permission.

When you need to assign geographic zones to records in PostgreSQL without overlap, the INT4RANGE type handles range storage, containment queries, and conflict enforcement natively without you writing a single manual comparison.

The problem: why range assignments fail silently with start/end fields

Here's a schema most people reach for first:

CREATE TABLE postal_code_assignments (
    assignment_id SERIAL PRIMARY KEY,
    agent_id INTEGER,
    start_code INTEGER,
    end_code INTEGER DEFAULT NULL
);

Single code lookups are fine:

SELECT agent_id
FROM postal_code_assignments
WHERE 19406 BETWEEN start_code AND COALESCE(end_code, start_code);

Overlap detection is not. To check whether an incoming range (A, B) conflicts with an existing (C, D), you have to manually cover: partial left overlap, partial right overlap, full containment in either direction, and single-value edge cases. That logic has to live somewhere in the application, it has to be replicated everywhere ranges are written, and the database will not catch it if you miss a path.

We built an admin tool for a sales operations team where agents are assigned postal code zones across a country. A silent overlap means one postal code shows up in two agents' zones, duplicated contacts, broken sales attribution, and no visibility into which assignment is wrong. We needed the database to enforce this, not the application.

INT4RANGE: what it is and how it works

INT4RANGE is a native PostgreSQL type that stores an integer range as a single value. The notation follows standard interval math:

[19400,19500]: 19400 through 19500 inclusive
[19306,19307): exactly 19306 (single value in canonical half-open form)
(19300,19400): all integers strictly between the bounds

The type ships with operators you actually need: && (overlap), <@ (containment), -|- (adjacency). These work with GiST indexes, so the database can run them efficiently at query and write time.

The schema:

CREATE TABLE postal_code_assignments (
    assignment_id SERIAL PRIMARY KEY,
    agent_id INTEGER,
    postal_code_range INT4RANGE,
    deleted BOOLEAN NOT NULL DEFAULT false,
    FOREIGN KEY (agent_id) REFERENCES sales_agents(agent_id),
    EXCLUDE USING GIST (postal_code_range WITH &&) WHERE (deleted IS NOT TRUE)
);

That EXCLUDE USING GIST line is the key part. It tells PostgreSQL to index the range column with GiST and reject any insert or update where the new range overlaps an existing one. The partial predicate WHERE (deleted IS NOT TRUE) excludes soft-deleted rows, so you can keep historical assignments without triggering false conflicts.

Inserts, lookups, and display

Single code:

INSERT INTO postal_code_assignments (agent_id, postal_code_range)
VALUES (4, '[19306,19307)');

Range:

INSERT INTO postal_code_assignments (agent_id, postal_code_range)
VALUES (5, '[19400,19500]');

Any overlapping insert is rejected immediately by the constraint. No pre-check query needed.

Ownership lookup:

SELECT agent_id
FROM postal_code_assignments
WHERE int4range(19406, 19407) <@ postal_code_range;

Readable display (PostgreSQL normalizes to half-open form, so subtract 1 from upper for inclusive end):

SELECT
  assignment_id,
  agent_id,
  lower(postal_code_range) AS start_postal_code,
  upper(postal_code_range) - 1 AS end_postal_code
FROM postal_code_assignments;

How does EXCLUDE USING GIST enforce non-overlapping zones?

EXCLUDE USING GIST is a table constraint that uses a GiST index to enforce that no two rows satisfy a given operator condition. In this case, the overlap operator && applied between any two rows.

GiST (Generalized Search Tree) is PostgreSQL's extensible indexing framework. Range types are designed to work with it, so the index can efficiently check whether an incoming value overlaps anything already in the table before the write is committed.

The WHERE (deleted IS NOT TRUE) partial predicate means soft-deleted rows are outside the constraint scope. Assignments that have been superseded stay in the table for audit purposes without blocking new assignments that cover the same zones.

Overlap validation moves from application code into the database itself. No pre-insert check query in your API, no validation function in your ORM, no race-condition-prone read-then-write pattern. The constraint fires on every write path.

Surfacing conflicts before writing updates

The GIST constraint blocks invalid writes but raises an error without context. In an admin UI, the admin needs to see both the incoming assignment and the existing conflicting record. A CTE handles this cleanly:

WITH updates AS (
    SELECT * FROM (VALUES
        (1::INT, 3::INT, '[19400,19450]'::int4range),
        (2::INT, 4::INT, '[19500,19600]'::int4range)
    ) AS t(id, sales_person_id, new_range)
),
conflicts AS (
    SELECT u.id AS update_id, p.id AS existing_id, p.postal_code_range
    FROM postal_code_assignments p
    JOIN updates u ON p.postal_code_range && u.new_range AND p.id != u.id
),
update_action AS (
    UPDATE postal_code_assignments p
    SET
      postal_code_range = u.new_range,
      agent_id = u.sales_person_id
    FROM updates u
    WHERE p.id = u.id
    AND NOT EXISTS (SELECT 1 FROM conflicts WHERE update_id = u.id)
    RETURNING p.id
)
SELECT 'conflict' AS result, c.update_id, c.existing_id, c.postal_code_range
FROM conflicts c
UNION ALL
SELECT 'update' AS result, a.id, NULL::INT, NULL::INT4RANGE
FROM update_action a;

Three stages: updates holds the incoming batch, conflicts checks each against existing rows using &&, update_action writes only the clean ones. The result set comes back tagged 'conflict' rows carry the overlapping record details, 'update' rows confirm what was saved. The application routes on the result column.

In our case, the updates CTE is populated via a Retool template literal, injecting a dynamic array at runtime. In a standard backend, replace that with a parameterized VALUES list from your application layer.

Tradeoffs worth knowing

INT4RANGE is PostgreSQL-specific. If you ever need to run this on MySQL or SQLite, you are back to the start/end approach. For Postgres-native stacks, the tradeoffs are almost entirely in its favour:

Overlap detection is a single operator, not a multi-condition WHERE
The constraint lives in the schema, not scattered across application code
GiST indexing performs well on containment and overlap queries at scale
The only real cost is a small write overhead from the GiST index; negligible for admin tool volumes, worth benchmarking at high insert rates

TL;DR

If you are on PostgreSQL and need non-overlapping integer ranges, INT4RANGE with EXCLUDE USING GIST is the right tool. It enforces the invariant at the database level, keeps overlap logic out of your application, and handles containment and conflict queries with native operators.

Happy to answer questions on the CTE pattern or the GiST constraint setup.