PostgreSQL INT4RANGE: enforce non-overlapping zones in SQL.

Postal code 19406 sits in two sales agents' zones. Both receive the customer's contact, sales attribution splits, and neither agent knows the assignment is wrong until something downstream fails. The overlap check lived in the app, and one update path skipped it.

That is the usual end of storing zones as start_code and end_code columns. This note replaces them with PostgreSQL's INT4RANGE type and an exclusion constraint, so the database rejects an overlapping assignment on every write path. It then adds a batch update that reports conflicts to the admin instead of failing silently.

Why start and end columns break down

The obvious schema is two integer columns:

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

Looking up one code is readable enough:

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

Overlap detection is the hard part. To check an incoming range against a stored one, the query has to catch partial overlap on either side. It also has to catch containment in either direction, and the single-value cases. The conditions grow quickly, have to be copied into every path that writes assignments, and nothing in the database enforces them. Miss the check in one path and invalid data goes in without an error.

What INT4RANGE stores

INT4RANGE is a built-in PostgreSQL type that stores a range of integers as one column value. It uses interval notation: square brackets are inclusive, parentheses are exclusive.

• [19400,19500] is every integer from 19400 to 19500, inclusive.

• [19306,19307) is exactly 19306.

• (19300,19400) is every integer strictly between 19300 and 19400.

PostgreSQL stores every integer range in half-open form, [lower,upper), so [19400,19500] is kept as [19400,19501). The type comes with operators for overlap (&&), containment (<@ and @>), and adjacency (-|-), all of which a GiST index can serve.

The schema change

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

The EXCLUDE USING GIST line does what the two-column schema cannot. It uses a GiST index to guarantee that no two rows satisfy postal_code_range && postal_code_range. Any insert or update whose range overlaps an active row is rejected before the write commits.

A UNIQUE constraint checks for equal values. An exclusion constraint checks any operator the index supports, and for zones the right rule is "no overlap", which equality cannot express. Range types have a built-in GiST operator class, so no extension is needed for this constraint.

The WHERE (deleted IS NOT TRUE) clause keeps soft-deleted rows out of the constraint. Superseded assignments can stay in the table as history without blocking new assignments for the same codes.

Writing and reading assignments

A single code is a one-value, half-open range. A span of codes is a closed range.

-- Single postal code
INSERT INTO postal_code_assignments (agent_id, postal_code_range)
VALUES (4, '[19306,19307)');

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

If either range overlaps an active row, PostgreSQL raises a constraint violation and the insert does not happen.

To find the agent for code 19406, build a one-value range and check containment:

SELECT agent_id
FROM postal_code_assignments
WHERE int4range(19406, 19407) <@ postal_code_range
  AND deleted IS NOT TRUE;

For the admin screen, lower() and upper() give the bounds. Subtract 1 from the upper bound to show the 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
WHERE deleted IS NOT TRUE;

Showing conflicts before the write

The constraint stops bad data, but its error carries no context. An admin who edits ten zones and gets one constraint error needs to see which change collided with which existing assignment.

This query checks a batch of edits, applies the ones without conflicts, and returns both outcomes in one result set. In Retool, the batch arrives as one JSON parameter, so no user input is spliced into the SQL.

WITH updates AS (
    SELECT id, agent_id, new_range::int4range AS new_range
    FROM jsonb_to_recordset({{ JSON.stringify(parse_updates.data ?? []) }}::jsonb)
      AS t(id INT, agent_id INT, new_range TEXT)
),
conflicts AS (
    SELECT u.id AS update_id, p.assignment_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.assignment_id <> u.id
     AND p.deleted IS NOT TRUE
),
update_action AS (
    UPDATE postal_code_assignments p
    SET postal_code_range = u.new_range,
        agent_id = u.agent_id
    FROM updates u
    WHERE p.assignment_id = u.id
      AND NOT EXISTS (SELECT 1 FROM conflicts c WHERE c.update_id = u.id)
    RETURNING p.assignment_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.assignment_id, NULL::INT, NULL::INT4RANGE
FROM update_action a;

The query runs in three stages. updates reads the incoming edits. conflicts checks each new range against every other active assignment with &&. update_action writes only the edits with no conflict.

The app splits the result on the result column. Conflict rows carry the existing assignment's id and range, so the screen can show both sides for the admin to resolve. Update rows confirm what was saved.

Two edits in the same batch can still overlap each other. The exclusion constraint catches that case and rejects the statement, so the data stays valid either way. {{ ... }} is Retool's syntax for a value from the app. Outside Retool, pass the JSON array as an ordinary query parameter.

The trade-offs

• Less code. Overlap, containment, and display come with the type. The two-column version needs the same logic written and maintained in the app.

• Indexing. A GiST index serves overlap and containment lookups directly. The two-column version needs multi-column indexes and more complex query plans.

• One readable rule. The constraint lives in the schema, where any developer can read it. Overlap logic in the app is spread across every path that writes.

• Portability. Range types are specific to PostgreSQL. If the stack has to run on a database without them, use the two-column pattern.

• Write cost. The GiST index adds a small cost to each insert and update. Admin tools write rarely, so it seldom matters. On a table with a high insert rate, measure it first.

The same pattern works for dates and times. DATERANGE, TSRANGE, and TSTZRANGE take the same operators and the same exclusion constraint, which suits bookings, shifts, and rota assignments.

Our view

We recommend INT4RANGE with an exclusion constraint for any assignment table where two rows must never cover the same value. The reason is enforcement: the rule holds on every write path, including the ones added next year. Keep the conflict query as well, because the admin needs to see which assignment blocked a change.

Full-text search with tsvector is another job worth moving from the app into PostgreSQL. Why your internal tools behave the way they do covers how rules like this one shape a tool's logic.