How to assign postal code zones to agents without overlap, using PostgreSQL's native range type and GIST constraint.
When you need to assign geographic zones to agents without overlapping coverage, PostgreSQL's INT4RANGE type handles range storage, containment queries, and conflict enforcement natively. It removes the manual comparison logic that start/end field approaches require, and lets the database reject invalid assignments before they reach your application layer.
What goes wrong when you store postal codes as start and end fields?
The obvious schema is two integer columns: start_code and end_code. It works for inserts and basic lookups. The problem surfaces when you need to check whether two ranges intersect.
A lookup for a single code is readable enough:
But overlap detection is a different problem. To check whether an incoming range (A, B) conflicts with an existing range (C, D), you have to catch every case where they share at least one value. That means writing conditions for partial left overlap, partial right overlap, full containment in either direction, and the single-value edge cases. The query logic grows quickly, has to be replicated across every path that touches assignments, and the database does not enforce it. If the check is missing from one update path, invalid data enters silently.
For an admin assigning zones to dozens of agents across a country, a silent overlap is operationally dangerous. A postal code appearing in two agents' zones means customer contacts get duplicated, sales attribution breaks, and neither agent knows the assignment is wrong until something fails downstream.
What is PostgreSQL INT4RANGE and how does it store postal code zones?
INT4RANGE is a built-in PostgreSQL type that stores a range of integers as a single column value. It uses mathematical interval notation: square brackets are inclusive, parentheses are exclusive.
[19400,19500]: all integers from 19400 through 19500 inclusive[19306,19307): exactly 19306 (inclusive lower, exclusive upper is how PostgreSQL normalizes single values)(19300,19400): all integers strictly between 19300 and 19400
The type ships with operators for overlap (&&), containment (<@, @>), adjacency (-|-), and union, all implemented efficiently via GiST indexing. You do not write the range comparison logic yourself, you use the operator.
The schema shift from start/end fields is small:
The EXCLUDE USING GIST line is doing something the start/end schema cannot: it enforces non-overlap at the database level. Any insert or update where the new range overlaps an existing active range is rejected before the write completes. Soft-deleted rows are excluded from the constraint, which means historical assignments can be retained without triggering false conflicts.
How do you insert single codes and ranges using INT4RANGE?
A single postal code is stored as a half-open range. [19306,19307) includes 19306 and excludes 19307, resolving to exactly one value. PostgreSQL normalizes range values to half-open form internally, so this is the canonical representation.
If the range on any insert overlaps an existing active record, PostgreSQL raises a constraint violation immediately. The write does not proceed.
How do you query which agent owns a given postal code in PostgreSQL?
The <@ operator checks whether a range is contained within another. To find the agent responsible for code 19406, construct a single-value range and check containment against the stored ranges:
To display ranges in a readable format for the admin interface, use the lower() and upper() functions. Because PostgreSQL stores ranges in half-open form, subtract 1 from the upper bound to get the inclusive end:
How does the EXCLUDE USING GIST constraint 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 condition is postal_code_range && postal_code_range — 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 index predicate means soft-deleted rows are outside the constraint scope. This matters in practice: assignments that have been superseded can stay in the table for audit purposes without blocking new assignments that cover the same zones.
The result is that overlap validation moves from application code into the database itself. You do not need a pre-insert check query in your API, a validation function in your ORM, or a race-condition-prone read-then-write pattern. The constraint fires on every write path.
How do you detect and surface conflicts before writing updates to the database?
The GIST constraint prevents invalid writes, but it raises an error without context. In an admin tool, that is not enough. The admin needs to see both the incoming assignment and the existing conflicting record to understand what needs resolving.
A CTE separates conflict detection from the write, and returns both outcomes in a single query:
The query runs in three stages. The updates CTE represents the incoming batch of changes, populated dynamically by the application. The conflicts CTE checks each incoming range against all existing records using the && operator, excluding the row being updated. The update_action CTE performs writes only for rows with no detected conflicts.
The final SELECT returns a unified result set. Rows tagged 'conflict' carry the existing record's ID and range. Rows tagged 'update' confirm the IDs that were saved. The calling application splits on the result column: conflicts surface in the UI for manual resolution, successful updates proceed without interruption.
Note: the
{{ parse_updates.data... }}block is a Retool template literal used to inject a dynamic array of updates from the frontend into the query at runtime. In a non-Retool context, replace this with parameterized input from your application layer.
What are the tradeoffs of using INT4RANGE compared to start and end fields?
Implementation speed. INT4RANGE covers most range management requirements out of the box. The overlap constraint, containment queries, and display functions are all built in. The start/end approach requires building and maintaining equivalent logic in application code.
Performance. GiST indexing on range types is efficient for containment and overlap queries at scale. Start/end field approaches require multi-column indexes and more complex query plans to achieve comparable performance on range lookups.
Maintainability. With INT4RANGE, the constraint lives in the schema. Any developer querying the table can read the EXCLUDE constraint and understand the invariant. With start/end fields, the overlap logic is scattered across the application, and removing or modifying it requires tracking down every location where range comparisons are made.
Portability. Range types are PostgreSQL-specific. If your stack requires a database that does not support them, the INT4RANGE approach is not transferable. The start/end pattern works across most relational databases.
Write overhead. The GiST index adds a small cost to every insert and update on the assignments table. For typical admin tool write volumes, this is not a concern. For tables with very high insert rates, it is worth benchmarking.
For the postal code zone use case, an admin tool with infrequent writes, a need for reliable conflict detection, and an audit trail requirement, INT4RANGE is the better fit on every axis that matters operationally.
FAQ
What does INT4RANGE do in PostgreSQL?
INT4RANGE stores a range of integers as a single column value with configurable inclusivity at each bound. It supports native operators for overlap, containment, and adjacency, and works with GiST indexes for efficient querying. It is one of several range types PostgreSQL provides alongside DATERANGE, TSRANGE, and others.
How does PostgreSQL prevent overlapping range inserts without application code?
The EXCLUDE USING GIST (column WITH &&) constraint tells PostgreSQL to use a GiST index to check whether any incoming value overlaps an existing row using the && operator. If it does, the write is rejected at the database level before the transaction commits, regardless of what the application layer does.
What is the difference between EXCLUDE USING GIST and a UNIQUE constraint in PostgreSQL?
A UNIQUE constraint checks for exact equality between values. EXCLUDE USING GIST checks for a condition defined by any operator supported by the index type. For ranges, this means you can enforce non-overlap (using &&) rather than non-equality, which is the correct invariant for geographic zone assignment.
How do you store a single integer value as an INT4RANGE in PostgreSQL?
Use a half-open interval where the upper bound is one greater than the value: '[19306,19307)'. This is also how PostgreSQL normalizes all INT4RANGE values internally, so using this format for single values keeps behavior consistent across inserts.
Can you use PostgreSQL range types for date or timestamp zones, not just integers?
Yes. PostgreSQL ships with DATERANGE, TSRANGE (without timezone), and TSTZRANGE (with timezone). The EXCLUDE USING GIST pattern and range operators work identically across all range types.
If you're building a zone management or assignment system and want to think through the data layer, the contact page is the right place to start.