Cross Join - канал о разработке

Узнал сегодня небольшой хак из мира 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"