UPSERT操作的列名不明确

Question

我正试图在postgres db-上运行UPSERT查询字符串

insert into business_data_test 
(select * from business_data_tmp) 
on conflict(phone_number) 
do update set 
average_expense=(average_expense*expense_count + excluded.average_expense*excluded.expense_count)/(expense_count + excluded.expense_count), expense_count=(expense_count + excluded.expense_count);

如果有冲突的数据，我基本上会尝试更新列average_expense，但我认为查询有问题，因为我遇到了以下错误-

ERROR:  column reference "average_expense" is ambiguous
LINE 1: ...lict(phone_number) do update set average_expense=(average_ex...
^
SQL state: 42702
Character: 123

我认为我们必须在某个地方添加一些表名别名，但我不确定如何解决这个问题。

Answer 1

您需要完全限定对旧值(即"非排除"值(的引用。如果您为目标表使用别名，这会更容易一些

insert into business_data_test as tst
select * 
from business_data_tmp 
on conflict(phone_number) 
do update 
set average_expense = (tst.average_expense * tst.expense_count + excluded.average_expense * excluded.expense_count)/(tst.expense_count + excluded.expense_count), 
expense_count = tst.expense_count + excluded.expense_count;

Answer 2

您的查询具有"排除在外"但没有提到那是什么"；排除"；是

我的建议是，使用一个单列值进行更新，而不是使用长计算逻辑。确保它正常工作，然后添加到更新逻辑中。。。。

这样更容易调试。