Advanced PostgreSQL Constraints: Unique, Primary & Exclusion

In PostgreSQL, ensuring data uniqueness extends beyond UNIQUE and PRIMARY KEY constraints. You can leverage ​**EXCLUDE constraints​ with ​GiST indexes**​ for flexible validation of complex conditions like spatial overlaps or range conflicts.

1. UNIQUE Constraint

​Purpose:​​ Enforce uniqueness on a column (or multi-column combination). Allow multiple NULL values (since NULL is treated as "unknown"). Automatically creates a B-Tree index.

​Example:​

DROP TABLE IF EXISTS customers;
CREATE TABLE customers (
    customerid INT UNIQUE,
    name TEXT
);

-- Insert valid data
INSERT INTO customers VALUES (1, 'Alice');   -- Success
INSERT INTO customers VALUES (NULL, 'David'); -- Success (multiple NULLs allowed)

-- Violation example
INSERT INTO customers VALUES (1, 'Charlie'); -- ERROR: Duplicate key

‍

Key Features:​​

• Multiple UNIQUE constraints per table.
• Auto-indexed for performance.
• Permits NULL values.

2. PRIMARY KEY Constraint

​Purpose:​​ Uniquely identify each row. Only one PRIMARY KEY per table. Implies NOT NULL and uniqueness.

​Example:​

DROP TABLE IF EXISTS customers_pk;
CREATE TABLE customers_pk (
    customerid INT PRIMARY KEY,
    name TEXT
);

-- Insert valid data
INSERT INTO customers_pk VALUES (1, 'Alice');   -- Success
INSERT INTO customers_pk VALUES (NULL, 'David'); -- ERROR: NULL in PRIMARY KEY

‍

Key Features:​​

• Single constraint per table.
• Combines UNIQUE and NOT NULL.
• Optimized indexing for joins and lookups.

3. EXCLUDE USING GIST Constraint

​Purpose:​​ Flexible validation for complex scenarios like spatial overlap, range conflicts, or custom operators. Requires a GiST index. Common use cases:

• Geometry types (point, box).
• Range types (int4range, tsrange).

Example: Spatial Overlap Detection

CREATE TABLE boxes (
    id SERIAL PRIMARY KEY,
    position BOX,
    EXCLUDE USING GIST (position WITH &&)
);

-- Insert non-overlapping boxes (success)
INSERT INTO boxes (position) 
VALUES ('( (0,0), (1,1) )');

-- Insert overlapping box (error)
INSERT INTO boxes (position) 
VALUES ('( (0.5,0.5), (1.5,1.5) )'); 
-- ERROR: Conflicting key violates exclusion constraint

‍

Example: Meeting Room Reservation System

Ensure a room isn’t double-booked during the same time slot (for confirmed reservations only):

CREATE EXTENSION IF NOT EXISTS btree_gist; -- Enables = operator for GiST

CREATE TABLE reservations (
    id SERIAL PRIMARY KEY,
    room TEXT NOT NULL,
    during TSTZRANGE NOT NULL,
    status TEXT DEFAULT 'pending',
    CONSTRAINT no_overlap_confirmed 
        EXCLUDE USING GIST (
            room WITH =,          -- Same room
            during WITH &&        -- Overlapping time ranges
        )
    WHERE (status = 'confirmed')
);

-- Valid insertions
INSERT INTO reservations 
VALUES (DEFAULT, 'Room A', '[2025-08-12 09:00, 10:00)', 'confirmed'); -- Success

INSERT INTO reservations 
VALUES (DEFAULT, 'Room B', '[2025-08-12 09:30, 10:30)', 'confirmed'); -- Success

-- Conflict detection
INSERT INTO reservations 
VALUES (DEFAULT, 'Room A', '[2025-08-12 09:30, 10:30)', 'confirmed'); 
-- ERROR: Time overlap with confirmed reservation

‍

Summary:​​

• Use UNIQUE for simple column uniqueness with NULL tolerance.
• Prefer PRIMARY KEY for table identity columns.
• Leverage EXCLUDE with GiST for advanced use cases like spatial/time/range validation.

This approach ensures robust data integrity tailored to your application’s needs.

‍