postgres Constraint Exclusion Constraints - ghdrako/doc_snipets GitHub Wiki

• https://www.postgresql.org/docs/current/sql-set-constraints.html
• https://www.postgresql.org/docs/current/ddl-constraints.html
• Explaining PostgreSQL xclusion constraints
• blog post about EXCLUDE USING gist... WITH exclusion constraints
• https://www.cybertec-postgresql.com/en/exclusion-constraints-in-postgresql-and-a-tricky-problem/

db=# ALTER TABLE orders ADD CONSTRAINT check_price_gt_zero CHECK (price >= 0);
ALTER TABLE
Time: 10745.662 ms (00:10.746)

This statement adds a check constraint on the price of an order, to make sure it's greater than or equal to zero. In the process of adding the constraint, the database scanned the entire table to make sure the constraint is valid for all the existing rows. The process took ~10s, and during that time, the table was locked.

NOT VALID

• https://hakibenita.com/postgresql-unknown-features#add-constraints-without-validating-immediately In PostgreSQL, you can split the process of adding a constraint into two steps.

First, add the constraint and only validate new data, but don't check that existing data is valid:

db=# ALTER TABLE orders ADD CONSTRAINT check_price_gt_zero CHECK (price >= 0) NOT VALID;
ALTER TABLE
Time: 13.590 ms

The NOT VALID in the end tells PostgreSQL to not validate the new constraint for existing rows. This means the database does not have to scan the entire table. Notice how this statement took significantly less time compared to the previous, it was almost instantaneous.

Next, validate the constraint for the existing data with a much more permissive lock that allows other operations on the table:

db=# ALTER TABLE orders VALIDATE CONSTRAINT check_price_gt_zero;
ALTER TABLE
Time: 11231.189 ms (00:11.231)

When check and foreign key constraints are created as NOT VALID there are not enforced on existing rows but only for new row changes. So they can by added safely to any tables even very big. When you cleanup and check all rows you can create NOT NULL or UNIQUE constraint and remove the old with 'NOT VALID' option.

Deferring Constraint Check

By default enforcment heppens as rows are inserted,updated, and deleted. Constraints support deferrable capability:

• UNIQUE
• PRIMARY KEY
• foreign key (REFERENCES)
• exclusion (EXCLUDE) but not support NOT NULL and CHECK.

You can set constraits defered during creation or by using:

SET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE }

Column constraint with multicolumn

NULLS DISTINCT lub NULLS NOT DISTINCT w ograniczeniach (UNIQUE, PRIMARY KEY) oraz w indeksach.

Example

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR NOT NULL,
    username VARCHAR NULL,
    UNIQUE (email, username)  -- problematic constraint
);

INSERT INTO users (email, username) VALUES ('[email protected]', NULL);
INSERT INTO users (email, username) VALUES ('[email protected]', NULL);

PostgreSQL pozwoli na dodanie obu wierszy, ponieważ wartości NULL w kolumnie username nie są traktowane jako równe – każdy NULL jest traktowany jako „brakująca” wartość. Z tego powodu ograniczenie unikalności (email, username) nie zostanie naruszone, ponieważ NULL w username nie jest porównywany z innym NULL.

Od wersji PostgreSQL 15 możesz zmienić to zachowanie, używając opcji NULLS DISTINCT lub NULLS NOT DISTINCT w ograniczeniach (UNIQUE, PRIMARY KEY) oraz w indeksach. Dzięki temu możesz kontrolować, czy NULL jest traktowany jako równy innemu NULL dla potrzeb unikalności.

• NULLS DISTINCT (domyślne) – NULL nie jest równy innemu NULL, co pozwala na wielokrotne dodanie NULL w kolumnach objętych ograniczeniem.
• NULLS NOT DISTINCT – NULL jest traktowany jako równy innemu NULL, więc dla ograniczeń unikalności dwa NULL w tej samej kolumnie będą traktowane jako naruszenie unikalności.

CREATE UNIQUE INDEX ON users (email, username) NULLS NOT DISTINCT;

Teraz tylko jeden wpis z NULL w username jest dozwolony dla tego samego emaila.

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

• https://gist.github.com/fphilipe/0a2a3d50a9f3834683bf

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;