在插入查询中多个`ON CONFLICT`的含义是什么？

Question

我有一张表格，其中包含列如id, name, start_date, end_date和2个唯一约束条件unique(id, name, start_date)和unique(id, name, end_date)。

现在当我为这个表编写插入查询时，我有类似以下的内容。

insert into table (id, name, start_date, end_date)
values (1, 'test', 'example-start-date', 'example-end-date')
on conflict (id, name, start_date) set something
on conflict (id, name, end_date) set something

但是有错误，这样做不被允许吗？

谢谢。

- Manoj ahirwar

---

根据手册记录，这是不允许的。 - user330315

Answer 1

答案取决于something。

• For DO NOTHING, the answer is simply to use a single clause without specifying the columns:

  ON CONFLICT DO NOTHING
  

  That can deal with conflicts on multiple unique constraints.

• For DO UPDATE, there is no straightforward solution. The syntax diagram in the documentation shows that you can only have a single ON CONFLICT clause that determines a single unique index.

  You could use procedural code to do it in the old-fashioned way, for example in PL/pgSQL:

  DECLARE
     v_constraint text;
  BEGIN
     LOOP  -- endless loop until INSERT or UPDATE succeeds
        BEGIN
           INSERT INTO ...;
  
           EXIT;  -- leave loop if INSERT succeeds
        EXCEPTION
           WHEN unique_violation THEN
              GET STACKED DIAGNOSTICS v_constraint := CONSTRAINT_NAME;
        END;
  
        CASE v_constraint
           WHEN 'constraint_name_1' THEN 
              UPDATE ...;
           WHEN 'constraint_name_2' THEN
              UPDATE ...;
        END CASE;
  
        EXIT WHEN FOUND;  -- leave loop if UPDATE changed a row
     END LOOP;
  END;
  

- Laurenz Albe