Обход NULL <> NULL для целочисленного столбца
У меня есть такая таблица в БД Postgres -
id dataset_id entity_id county_state_id data
34 31 33413 341 JSOB object
35 31 33413 342 JSOB object
36 31 33413 JSOB object
Я хочу вставить или обновить эту таблицу на основе того, существует ли запись в таблице. Для этого я написал следующий запрос:
INSERT INTO entity (id, dataset_id, entity_id, county_state_id, data)
SELECT
nextval('id_seq'),
(SELECT id FROM dataset WHERE name = 'Payer'),
e.id,
NULL
jsonb_build_object
('a', a,
'b', b,
'c', c,
)
from
entity e
JOIN payer p
ON p.id = e.id
ON CONFLICT (dataset_id, entity_id, data, county_state_id)
DO NOTHING;
Я вставляю следующий ввод в таблицу -
id dataset_id entity_id county_state_id data
37 31 33413 JSOB object
Я ожидаю, что приведенный выше SQL-запрос не обновит никаких записей, потому что эта запись уже существует в таблице. Но он вставляет запись. Я подозреваю, что это происходит, потому что NULL <> NULL и я пытаюсь вставить NULL в столбец county_state_id. Это целочисленный столбец, поэтому я не могу вставить в него пустую строку, поэтому я не знаю, как заставить Postgres распознать, что указанная выше запись уже существует в таблице.
@AdrianKlaver Да, но я не хочу вставлять ноль, потому что нет county_state_id, который равен 0.
Затем создайте фиктивную запись со значением county_state_id, равным 0, например. 'no_county_state_id`.
Ответы 2
Если вы хотите предотвратить дублирование, вам нужен уникальный индекс или ограничение. Для этого вам понадобятся два из них:
-- handle not-NULL case
alter table t add constraint unqc_entity_4 unique (dataset_id, entity_id, data, county_state_id);
alter table t add constraint unqc2_entity_4 unique (dataset_id, entity_id, data, (case when county_state_id is null then -1 else id end);
К счастью, do nothing применяется ко всем ограничениям, если они не указаны, поэтому вы можете сформулировать вставку как:
INSERT . . .
ON CONFLICT DO NOTHING;
Здесь маленькая db<>рабочий пример, иллюстрирующая концепцию.
У меня есть уникальный индекс, поэтому я могу использовать предложение ON CONFLICT.
@Аарон. . . NULLs сложны в этом контексте, но есть довольно простой обходной путь.
@GordonLinoff, я предпочитаю избегать использования специальных значений, таких как -1, вместо NULL. Postgres поддерживает отфильтрованные/частичные индексы, которые, как мне кажется, здесь вполне подходят. Смотрите мой ответ.
Похоже, здесь подойдет отфильтрованный/частичный уникальный индекс.
Точнее, два индекса.
-- this takes care of non-null duplicates
CREATE UNIQUE INDEX IX_entity_NON_NULL ON entity
(dataset_id, entity_id, county_state_id);
-- this prevents duplicates when county_state_id IS NULL
CREATE UNIQUE INDEX IX_entity_NULL ON entity
(dataset_id, entity_id)
WHERE (county_state_id IS NULL);
При таком подходе вам не нужно использовать какие-то специальные значения, такие как 0 или -1 вместо значений NULL.
Из вопроса мне непонятно, нужно ли включать поле data в индекс, включайте, если нужно.
Как бы выглядел мой пункт insert или on conflict, если бы я сделал это таким образом?
@ Аарон, я думаю, что твое заявление INSERT остается прежним. Вы не сможете вставить вторую строку с NULL county_state_id и повторением dataset_id, entity_id. Частичный уникальный индекс предотвратит это.
Да, но вы могли бы
INSERT0.