Cross Join - канал о разработке: “Узнал сегодня небольшой хак из мира Postgres. Например, у вас есть таб…”

Узнал сегодня небольшой хак из мира Postgres. Например, у вас есть таблица с купонами, и у купонов есть некая дата устаревания valid_until. Допустим, вам надо обеспечить такой констрейнт, чтобы у одного человека не было одновременно больше одного неустаревшего купона.

Например, таблица изначально выглядит так:


CREATE TABLE coupons (
    id  bigint primary key generated by default as identity,
    user_id bigint not null,
    created_at timestamp not null,
    valid_until timestamp not null
)

Думаю, всем известно, что есть ключевое слово UNIQUE, с помощью которого можно обеспечить уникальность какого-то поля/полей. Но напрямую оно нам не подойдет, так как в базе могут остаться устаревшие купоны, которые будут конфликтовать со свежими.

Также многим известно, что UNIQUE может идти с условием. Казалось бы, добавить просто к констрейнту WHERE (valid_until > NOW()) и дело с концом. Однако так нельзя: невозможно сделать констрейт по функции, которую заранее не вычислить. Т.е. WHERE (is_valid) бы прокатило, но не сравнение с NOW().

Можно выкрутиться с помощью триггера, но есть способ поэлегантнее.

В посгресе есть тип данных tsrange (диапазон времени), а также ключевое слово EXCLUDE, с помощью которого можно запретить вставку строк, где диапазоны пересекаются:

EXCLUDE USING GIST (tsrange(created_at, valid_until) WITH &&)

Но это условие действует на всю таблицу, а не в рамках юзера. Поэтому надо добавить еще одно:

EXCLUDE USING GIST (user_id WITH =, tsrange(created_at, valid_until) WITH &&)

(чтобы это работало, нужно сначала включить расширение btree_gist: CREATE EXTENSION IF NOT EXISTS btree_gist;)

Итого, проверяем:


CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE coupons (
    id  bigint primary key generated by default as identity,
    user_id bigint not null,
    created_at timestamp not null,
    valid_until timestamp not null,
    EXCLUDE USING GIST (
        user_id WITH =, 
        tsrange(created_at, valid_until) WITH &&
    )
);

INSERT INTO coupons(user_id, created_at, valid_until)
VALUES
(100500, now(), now() + interval '1 day');

INSERT INTO coupons(user_id, created_at, valid_until)
VALUES
    (100501, now(), now() + interval '10 day');

INSERT INTO coupons(user_id, created_at, valid_until)
VALUES
    (100500, now(), now() + interval '10 day');

последний insert не пройдёт: "ERROR: conflicting key value violates exclusion constraint"