PostgreSQL在多列唯一约束条件下的ON CONFLICT操作

Question

我在使用PostgreSQL进行UPSERT时遇到了一个不理解的行为。文档似乎表明，INSERT语句的冲突目标可以是索引表达式或约束名称。然而，当我尝试引用约束名称时，会出现“列...不存在”的错误。

我的第一次尝试只是创建一个UNIQUE索引，这对于约束推断来说是有效的：

create table kv (key text, value text, extra text);
create unique index kv_key_value on kv(key, value);
insert into kv (key, value) values ('k1', 'v1');
-- this works:
insert into kv (key, value, extra) values ('k1', 'v1', 'e1')
  on conflict (key, value) do update set extra=excluded.extra;

-- this does not
insert into kv (key, value, extra) values ('k1', 'v1', 'e1')
  on conflict (kv_key_value) do update set extra=excluded.extra;

描述上表，我在“索引”下看到以下内容：

"kv_key_value" UNIQUE, btree (key, value)

我的第二次尝试是在创建表时显式地添加唯一约束：

create table kv (
  key text,
  value text,
  extra text,
  constraint kv_key_value unique(key, value));

描述上表，"Indexes:" 的输出略有不同（与先前示例中的 "UNIQUE" 相比为 "UNIQUE CONSTRAINT"）:

"kv_key_value" UNIQUE CONSTRAINT, btree (key, value)

然而，我仍然无法将约束名称指定为冲突目标：

insert into kv (key, value, extra) values ('k1', 'v1', 'e1')
  on conflict (kv_key_value) do update set extra=excluded.extra;
ERROR:  column "kv_key_value" does not exist
LINE 2:       on conflict (kv_key_value) do update set extra=exclude...

我这里有什么误解吗？我完全理解我可以使用等效表达式并依靠约束推断，但我想知道为什么约束名称似乎不起作用，而文档却表明它应该起作用?

- coleifer

Answer 1

您的语法有误。

对于一个约束条件，它应该是：

INSERT INTO kv (key, value, extra)
   VALUES ('k1', 'v1', 'e1')
   ON CONFLICT ON CONSTRAINT kv_key_value
      DO UPDATE SET extra = excluded.extra;

- Laurenz Albe

---

非常感谢您！ - coleifer

请使用其他已记录的语法。 - Laurenz Albe

我想要加入我的五分钱。对于这个主题，(1)表上的“唯一约束”和(2)“唯一索引”（特别是当它是部分的时候）有所不同。对于(1)，可以使用ON CONSTRAINT子句，并且它会起作用；对于(2)，则必须使用类似于ON CONFLICT (name, companyId) WHERE companyId IS NOT NULL的东西——完整的(2)-索引表达式（请参见PG-docs中的index_expression和index_predicate）。在注释中压缩所有这些信息是困难的，而我不是天才 :'( - maxkoryukov

@maxkoryukov，也许你可以写一个答案。 - Laurenz Albe