postgres Exclusion Constraints - ghdrako/doc_snipets GitHub Wiki

Parameter constraint_exclusion

constraint_exclusion [on|off]

If you use partitioning, you should not disable constraint_exclusion, otherwise you won't have any advantages from partitioning during query execution, because all partitions will always be scanned.

On the other hand, if you don't use partitioning, and you have no check constraints on tables whose condition appears in queries regularly, it will save some planning time to disable constraint_exclusion.

Exclusion constraint

Exclusion constraints are used to define an expression returning a true/false result and only insert data if you get a false response. A true response will mean that this data already exists, so you can’t insert. A false response will mean the data does not yet exist and you can insert. Common uses for constraint exclusions are adding roles for a user that can only have one role or adding a calendar reservation for someone that already has that time booked.

Exclusion constraints commonly work with the idea that we have a box and that box is either filled in or not. And the box is sized based on the data points, like a time range.

An exclusion constraint will probably have:

  • EXCLUDE statement
  • the GIST statement
  • A box definition with points
  • && which is a operator letting you know if a bounding box intersects another bounding box
CREATE EXTENSION btree_gist;


ALTER TABLE public.reservations ADD CONSTRAINT reservation_overlap
EXCLUDE USING GIST (
        box (
            point(
                extract(epoch from start_time),
                room_id
            ),
            point(
                extract(epoch from end_time) - 0.5,
                room_id + 0.5
            )
        )
        WITH &&
    );

ALTER TABLE my_table
  ADD CONSTRAINT no_overlap 
  EXCLUDE USING gist (product_id WITH =, applicable_period WITH &&)
WHERE (user_id is null);

CREATE TABLE booking(
    room integer PRIMARY KEY,
    during tstzrange NOT NULL
);
CREATE EXTENSION btree_gist;

ALTER TABLE booking ADD CONSTRAINT no_intersect
    EXCLUDE USING gist(room WITH =, during WITH &&);
-- Different rooms from different sections can be booked for the same periods of time:


INSERT INTO booking(room, during) VALUES
    (1, '[today 13:00,today 16:00)'::tstzrange),
    (11, '[today 13:00,today 16:00)'::tstzrange);

INSERT 0 2
-- But you can’t book the same room for overlapping time ranges:


INSERT INTO booking(room, during)
    VALUES (1, '[today 14:00,today 18:00)'::tstzrange);

ERROR:  duplicate key value violates unique constraint "booking_1_10_pkey"
DETAIL:  Key (room)=(1) already exists.



CREATE EXTENSION btree_gist;

CREATE TABLE room_reservations (
    room_id integer,
    reserved_at timestamptz,
    reserved_until timestamptz,
    canceled boolean DEFAULT false,
    EXCLUDE USING gist (
        room_id WITH =, tstzrange(reserved_at, reserved_until) WITH &&
    ) WHERE (not canceled)
);
INSERT INTO room_reservations (room_id, reserved_at, reserved_until) VALUES
    (1, '2015-01-01 00:00', '2015-01-02 00:00'),
    (2, '2015-01-01 00:00', '2015-01-02 00:00');

-- Creating an overlapping reservation for room 1 is not possible:
INSERT INTO room_reservations (room_id, reserved_at, reserved_until) VALUES
    (1, '2015-01-01 10:00', '2015-01-02 14:00');
-- ERROR:  conflicting key value violates exclusion constraint "room_reservations_room_id_tstzrange_excl"
-- DETAIL:  Key (room_id, tstzrange(reserved_at, reserved_until))=(1, ["2015-01-01 10:00:00+00","2015-01-02 14:00:00+00")) conflicts with existing key (room_id, tstzrange(reserved_at, reserved_until))=(1, ["2015-01-01 00:00:00+00","2015-01-02 00:00:00+00"))])]).

-- Same for room 2:
INSERT INTO room_reservations (room_id, reserved_at, reserved_until) VALUES
    (1, '2014-12-31 23:00', '2015-01-01 00:01');
-- ERROR:  conflicting key value violates exclusion constraint "room_reservations_room_id_tstzrange_excl"
-- DETAIL:  Key (room_id, tstzrange(reserved_at, reserved_until))=(1, ["2014-12-31 23:00:00+00","2015-01-01 00:01:00+00")) conflicts with existing key (room_id, tstzrange(reserved_at, reserved_until))=(1, ["2015-01-01 00:00:00+00","2015-01-02 00:00:00+00")).

-- Canceling the reservation for room 1 makes it possible to add a new
-- reservation overlapping the canceled one:
UPDATE room_reservations SET canceled = true WHERE room_id = 1;
INSERT INTO room_reservations (room_id, reserved_at, reserved_until) VALUES
    (1, '2015-01-01 10:00', '2015-01-02 14:00');

-- Undoing the cancellation is not possible due to the new reservation.
UPDATE room_reservations SET canceled = false WHERE room_id = 1;
-- ERROR:  conflicting key value violates exclusion constraint "room_reservations_room_id_tstzrange_excl"
-- DETAIL:  Key (room_id, tstzrange(reserved_at, reserved_until))=(1, ["2015-01-01 00:00:00+00","2015-01-02 00:00:00+00")) conflicts with existing key (room_id, tstzrange(reserved_at, reserved_until))=(1, ["2015-01-01 10:00:00+00","2015-01-02 14:00:00+00")).

Find constraints

SELECT * FROM (
    SELECT
       c.connamespace::regnamespace::text as table_schema,
       c.conrelid::regclass::text as table_name,
       con.column_name,
       c.conname as constraint_name,
       pg_get_constraintdef(c.oid)
    FROM
        pg_constraint c
    JOIN
        pg_namespace ON pg_namespace.oid = c.connamespace
    JOIN
        pg_class ON c.conrelid = pg_class.oid
    LEFT JOIN
        information_schema.constraint_column_usage con ON
        c.conname = con.constraint_name AND pg_namespace.nspname = con.constraint_schema
    UNION ALL
    SELECT
        table_schema, table_name, column_name, NULL, 'NOT NULL'
    FROM information_schema.columns
    WHERE
        is_nullable = 'NO'
) all_constraints
WHERE
    table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY table_schema, table_name, column_name, constraint_name;