Postgresql Exclusion constraints, хочется странного?

Question

[Viktor Maksimov]

Доброго дня коллеги.

Есть некая таблица :

CREATE TABLE test_good
(
    id BIGINT PRIMARY KEY NOT NULL,
    good_id UUID DEFAULT uuid_generate_v4() NOT NULL,
    by_default BOOLEAN DEFAULT false NOT NULL,
);

Требуется, при вставке и обновлении данных ограничить значение поля by_default, только одним true в пределах одного good_id. Т.е. получаем флаг "запись по умолчанию".
Т.е. если среди 5 имеющихся записей для good_id N есть запись в которой колонка by_default выставлена в true, все остальные должны быть гарантированно false. Установка флага на другое поле должно приводить к инвертированию флага у существующего.
Как-то так..

Хочется сделать это средствами БД, что-то подсказывает, что может подойти Exclusion constraints, но не могу сообразить как именно.

Answer 1

[Илья Ерохин]

Как-то это не очень в духе реляционных бд. Можно например создать таблицу defaults с group_id(unique) и default_item_id, если для группы может быть выбран один или ни одного итема.

Comments

[Viktor Maksimov]

Такой вариант я рассматривал, отмел так как не хочу лишних сущностей вводить. При таком раскладе проще наваять функцейку и обновлять флаги отдельно от остальных данных.. Говорю же хочется чтобы БД сушила голову о том как это сделать, нужно только указать критерии.

Answer 2

[Melkij]

CREATE UNIQUE INDEX test_good_default_goods ON test_good (good_id)
WHERE by_default = true

Это ограничение целостности, чтобы не отстрелить себе ногу.

Обновление:
Вместо

update test_good set by_default = true where id = :id

и какой-то фоновой магии, обновлять вот так:

update test_good set by_default = (id = :new_default_id) where good_id = :good_id

Если надо, uuid можно подзапросом по тому же самому :new_default_id вытащить.
Впрочем, возможно этот апдейт споткнётся на уникальном индексе, не помню точно, когда уникальность проверяется - для всего выражения или для каждой строки. Ну, можно будет переписать запрос. В конце концов, у нас ещё есть CTE.