повторяющееся значение ключа нарушает уникальное ограничение с конфликтом, не работает

Я пытаюсь использовать конфликт с уникальным в нескольких полях. У меня есть эта структура.

|---------------------|------------------|
|      id             |     uuid         |
|---------------------|------------------|
|      name           |     string       |
|---------------------|------------------|
|      field_a        |     uuid         |
|---------------------|------------------|
|      field_b        |     uuid         |
|---------------------|------------------|
|      field_c        |     uuid         |
|---------------------|------------------|

field_a,field_b,field_c уникальны, а field_b может быть NULL.

Это мой запрос:

INSERT INTO table (field_a, field_b,field_c,  name) 
values ('434d1d67-df03-4310-b3eb-93bf1c6e319e',
        'd3a3745e-ad97-4fcd-1fed-26bb406dc265',
        'd5a4232e-ad56-6ecd-5fed-25bb106dc114')
on conflict(field_a,field_b,field_c) 
do update
  set  name = 'abc'

Если я снова попробую это с тем же запросом, он сработает. Он обновляется в случае конфликта. Но когда я использую null следующим образом:

INSERT INTO 
table (field_a, field_b,field_c,  name) 
values ('434d1d67-df03-4310-b3eb-93bf1c6e319e',
        null,
        'd5a4232e-ad56-6ecd-5fed-25bb106dc114')
on conflict(field_a,field_b,field_c) 
do update
set  name = 'abc'

Это не работает. Это добавит новую строку в мою таблицу. Чтобы предотвратить добавление новой строки, я создал индекс и установил значения NULL, подобные этому

CREATE 
UNIQUE INDEX uidx_uniq ON table USING btree (
   (COALESCE(field_a, '00000000-0000-0000-0000-000000000000'::uuid)),
   (COALESCE(field_a, '00000000-0000-0000-0000-000000000000'::uuid)),
   (COALESCE(field_a, '00000000-0000-0000-0000-000000000000'::uuid)))

Это не позволяет добавлять новое значение в БД, если оно существует с нулевым значением, но при конфликте с этим не работает, это дает мне ошибку:

duplicate key value violates unique constraint "uidx_uniq"

Как я могу решить это с помощью null?


postgresql null sql unique-index
person Zunnurain Badar    schedule 28.01.2019    source источник

Ответы (2)


arrow_upward
0
arrow_downward

Как сказано в в документации:

Нулевые значения не считаются равными.

Таким образом, нет конфликта, если одно из значений равно NULL.

Вы не можете использовать уникальный индекс, который вы создали с предложением ON CONFLICT, потому что там вы можете использовать только уникальные ограничения. Уникальные ограничения не могут быть определены для выражений, только для столбцов.

Возможно, вам следует использовать другое значение, отличное от NULL, чтобы смоделировать то, что вы имеете в виду. NULL означает неизвестное в SQL, поэтому интерпретация PostgreSQL имеет смысл.

person Laurenz Albe    schedule 28.01.2019
comment
Да (если я правильно понимаю), но я не считаю нуль нулевым, мой нуль означает 00000000-0000-0000-0000-000000000000. - person Zunnurain Badar; 28.01.2019
comment
Затем, во что бы то ни стало, используйте это значение, а не NULL. Тогда все будет работать так, как вы хотите, и в качестве положительного побочного эффекта ваши запросы могут стать проще (и, следовательно, быстрее). - person Laurenz Albe; 28.01.2019
comment
Я попытался поставить «00000000-0000-0000-0000-000000000000» вместо нуля, это также не работает, но если я поставлю «00000000-0000-0000-0000-000000000001» вместо нуля, это сработает. - person Zunnurain Badar; 28.01.2019
comment
Оба значения будут работать одинаково. Должно быть, произошло какое-то недоразумение. - person Laurenz Albe; 28.01.2019

arrow_upward
0
arrow_downward

Я думаю, вам также нужен отфильтрованный уникальный индекс:

CREATE UNIQUE INDEX uidx_uniq2 ON table (field_a, field_c)
    WHERE field_b IS NULL;

Вам нужно будет проверить оба индекса на конфликты в ON CONFLICT.

person Gordon Linoff    schedule 28.01.2019