不同表大小的表的UPSERT

Question

我得到错误:

ERROR: column "some_col_name" does not exist Hint: There is a column named "some_col_name" in table "usert_test", but it cannot be referenced from this part of the query.

OnUPSERT这个错误的原因是源表(从API中读取)并不总是具有与我正在寻找UPSERT的表相同数量的字段。在UPSERT过程中是否有办法处理这个问题?到目前为止，我尝试了以下方法:

INSERT INTO scratch."usert_test" (many_cols)
SELECT *
FROM scratch.daily_scraper
ON CONFLICT (same_unique_id)
DO UPDATE
SET 
many_fields = excluded.many_fields;

Answer 1

在每个实例中指定每个列的名称。

insert into scratch."usert_test" (column_name1, column_name2, column_name3,column_name3)
select cola, colb, colc, colf
from scratch.daily_scraper 
on conflict (column_name1, column_name4)
do update
set 
column_name3  = excluded.column_name3
, column_name2  = excluded.column_name2; 

您有多少列正确命名每个列。(恕我直言)就像你应该做的那样。