问题:重复键值违反唯一约束,冲突不起作用

我正在尝试在多个字段上使用唯一的冲突。我有这个结构。

|---------------------|------------------|
|      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)))

这不允许在 db 中添加新值,如果任何存在 null 但在冲突中不起作用,这会给我错误:

duplicate key value violates unique constraint "uidx_uniq"

如何用 null 解决这个问题?

解答

正如文档所说:

Null 值不被视为相等。

因此,如果其中一个值为 NULL,则不存在冲突。

您不能使用通过ON CONFLICT子句创建的唯一索引,因为您只能在此处使用唯一的_constraints_。唯一约束不能在表达式上定义,只能在列上。

也许您应该使用与 NULL 不同的值,以便模拟您的意思。 NULL 在 SQL 中意味着“未知”,所以 PostgreSQL 的解释是有道理的。