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