EXCLUSION CONSTRAINTSDDL
Buy Me A Coffee

A generalization of UNIQUE constraints that uses arbitrary operators — most commonly used to prevent overlapping date/time ranges or spatial objects.

PostgreSQL constraint that ensures no two rows overlap in a specified way, using exclusion operators (e.g., cannot overlap in time). Implemented with a GiST index. Essential for scheduling systems where double-booking must be prevented.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✗ Not Supported — Not supported. Preventing overlapping ranges requires triggers or application logic.
PostgreSQL ✓ Supported 9.0 EXCLUDE USING index_method (col WITH op, ...). Uses GiST or SP-GiST indexes. The btree_gist extension is usually needed to mix btree-comparable columns (like INT) with range/geometric operators. Can use any operator with an appropriate operator class.
SQL Server ✗ Not Supported — Not supported. Only UNIQUE and CHECK constraints available; overlap prevention requires triggers.
Oracle ✗ Not Supported — Not supported. Only UNIQUE and CHECK constraints available.
SQLite ✗ Not Supported — Not supported. Only UNIQUE and CHECK constraints available.

Details

Unique to PostgreSQL. Enables database-enforced non-overlap rules (e.g., no two reservations for the same room in the same time window) declaratively, without triggers or application checks.

Standard Syntax

CREATE TABLE bookings ( room INT, during TSRANGE, EXCLUDE USING gist (room WITH =, during WITH &&) );

Version Support

MySQL: Not supported PostgreSQL: Since 9.0 SQL Server: Not supported Oracle: Not supported SQLite: Not supported

Per-Database Syntax & Notes

MySQL

Not supported. Preventing overlapping ranges requires triggers or application logic.

-- Not supported.

PostgreSQL

EXCLUDE USING index_method (col WITH op, ...). Uses GiST or SP-GiST indexes. The btree_gist extension is usually needed to mix btree-comparable columns (like INT) with range/geometric operators. Can use any operator with an appropriate operator class.

-- Prevent room double-booking: CREATE EXTENSION btree_gist; -- usually required CREATE TABLE bookings ( room INT, during TSRANGE, EXCLUDE USING gist (room WITH =, during WITH &&) ); -- No two rows can share the same room AND have overlapping time ranges.

SQL Server

Not supported. Only UNIQUE and CHECK constraints available; overlap prevention requires triggers.

-- Not supported.

Oracle

Not supported. Only UNIQUE and CHECK constraints available.

-- Not supported.

SQLite

Not supported. Only UNIQUE and CHECK constraints available.

-- Not supported.