postgresql ON CONFLICT ON CONSTRAINT for 2 constraints

Question

我目前正在使用 PostgreSQL 9.5，想知道是否有可能在 ON CONFLICT ON CONSTRAINT 语句中包含 2 个约束的名称。我的sql如下

INSERT INTO LIVE.TABLE (column1, column2, column3)
SELECT DISTINCT ON (cloumn1) column1, column2, column3
FROM STAGE.TABLE
​ON CONFLICT ON CONSTRAINT live.table.pkey DO NOTHING

这很好用，但是我想做的是在 ON CONFLICT ON CONSTRAINT 语句中包含第二个约束。我尝试了以下选项，但它似乎对我不起作用。

INSERT INTO LIVE.TABLE (column1, column2, column3)
SELECT DISTINCT ON (cloumn1) column1, column2, column3
FROM STAGE.TABLE
​ON CONFLICT ON CONSTRAINT live.table.pkey, live.table.fkey1 DO NOTHING

任何建议将不胜感激。

Answer 1

似乎你不能附加两个约束名称

ON CONFLICT ON CONSTRAINT live.table.pkey, live.table.fkey1 DO NOTHING

但是你可以

• 冲突时 ( col1, col2 ) 什么都不做。

或

• 遇到冲突什么都不做。

Specifies which conflicts ON CONFLICT takes the alternative action onby choosing arbiter indexes. Either performs unique index inference,or names a constraint explicitly. For ON CONFLICT DO NOTHING, it isoptional to specify a conflict_target; when omitted, conflicts withall usable constraints (and unique indexes) are handled. For ONCONFLICT DO UPDATE, a conflict_target must be provided.

https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT