如何使用postgres插入...... ON CONFLICT在从选择语句插入时进行更新

Question

我有以下三张桌子。请原谅表名，它们是通用数据库结构命名转换的一部分。

          table: tbl_331
          id (primary key, integer)
          field_3 (text)

          table: tbl_329_customid
          id (primary key, integer)
          fullname (text)

          table: tbl_331_to_tbl_329_field
          tbl_331_id (integer)
          tbl_329_id (integer)
          primary key (tbl_331_id, tbl_329_id)

我想编写一个查询，使用匹配的值将值插入多对多表：tbl_331_to_tbl_320_字段。如果我使用DO NOTHING参数，这是有效的。

此语句用于插入找到匹配的值。

    insert into schema_1.tbl_331_to_tbl_329_field_3 
    select  X.id, S.id from schema_1.tbl_329 S 
    JOIN schema_1.tbl_331_customid X on S.field_3=x.fullname
    ON CONFLICT DO NOTHING

但是我想使用DO UPDATE，所以如果有冲突，它将更新第二个id（S.id）。例如，如果我将S.field_3中的值更改为与新ID匹配的其他值，我想更新这个多对多表中的ID。

   insert into schema_1.tbl_331_to_tbl_329_field_3 
    select  X.id, S.id from schema_1.tbl_329 S 
    JOIN schema_1.tbl_331_customid X on S.field_3=x.fullname
    ON CONFLICT ( tbl_331_id, tbl_329_id ) DO UPDATE set S.id = EXCLUDED.id

“这会导致'id'列的错误消息”不明确。"

如果我尝试使用这样的别名：

    insert into schema_1.tbl_331_to_tbl_329_field_3 
    select  X.id, S.id as myalias from schema_1.tbl_329 S 
    JOIN schema_1.tbl_331_customid X on S.field_3=x.fullname
    ON CONFLICT ( tbl_331_id, tbl_329_id ) DO UPDATE set S.id = myalias

我得到“列myalias不存在”

我对DO UPDATE的这种语法非常陌生。我的假设是，在发生冲突之后，必须指定它将发生冲突的uniqye/主键，然后告诉它要更新什么。

我该如何编写它，以便它将匹配的ID更新为新的匹配ID？

Answer 1

insert into schema_1.tbl_331_to_tbl_329_field_3 
select  X.id, S.id from schema_1.tbl_329 S 
JOIN schema_1.tbl_331_customid X on S.field_3=x.fullname
ON CONFLICT ( tbl_331_id, tbl_329_id ) DO UPDATE set S.id = EXCLUDED.id

1. 这种类型的UPDATE的上下文只包括名为的特殊表本身。您的输入表在更新中不可见，您只能看到冲突的记录（排除）以及您试图将它们插入的字段（schema_1.tbl_331_to_tbl_329_field_3）。因此，您的S无法访问，也不需要位于冲突的中。。。DO UPDATE您正在解决传入数据与目标表中已有数据之间的冲突——Postgres不介意您从哪里获得数据，甚至不尝试记住
2. 错误：列引用“id”不明确非常不直观。即使您可以随意在输入中为列添加别名，只要它们的顺序和类型与您的目标相匹配，但在冲突中，它们会像已经在目标表中一样被处理。因此，您的第一个id和第二个id（或以后的myalias）实际上已经在排除的表中分别被称为tbl_331_id和tbl_329_id。出现错误的原因是，Postgres首先检查歧义，然后立即在该点抛出错误，然后再检查这些列是否属于当前上下文
3. S.id=myalias没有多大意义，因为你刚刚把它化名为myalias