PostgreSQL，插入与选择冲突时失败做更新

Question

我试图解决在第12页中插入pk失败时进行更新的问题。我认为，这很容易，只是查询中的冲突。。。但是没有。当我运行查询时，我得到了这个错误：

ERROR:  column excluded.fec does not exist
LINE 25: ...ON CONFLICT (address) DO UPDATE SET saldo.fecha = EXCLUDED.f...
^
SQL state: 42703
Character: 1052

表格：

CREATE UNLOGGED TABLE wallet.saldo
(
address character varying(42) COLLATE pg_catalog."default" NOT NULL,
fecha timestamp without time zone NOT NULL,
saldo numeric(60,20) NOT NULL,
CONSTRAINT wallet_saldo_address_pk PRIMARY KEY (address)
)

查询：

INSERT INTO wallet.saldo(address, fecha, saldo)
select  '0xaa515035c04aab' as token_address,  max(block_timestamp)::date as fec, sum(importe) as imp 
from (
SELECT 
tt.block_timestamp, tt.value::numeric(60,20) / ('1' || left('0000000000000000000000000', t.decimals))::numeric * -1 as importe
FROM raw.token_transfers tt inner join raw.tokens t on t.address = tt.token_address
where to_address = '0xaa515035c04aab'
and tt.block_timestamp between '20201009 00:00:00' and '20201009 23:59:59.99999'
union
SELECT 
tt.block_timestamp, (tt.value::numeric(60,20) / ('1' || left('0000000000000000000000000', t.decimals))::numeric) as importe
FROM raw.token_transfers tt inner join raw.tokens t on t.address = tt.token_address
where from_address = '0xaa515035c04aab'
and tt.block_timestamp between '20201009 00:00:00' and '20201009 23:59:59.99999'
) as det
ON CONFLICT (address) DO UPDATE SET saldo.fecha = EXCLUDED.fec,  saldo.saldo = EXCLUDED.imp
;

select DET工作正常，返回正确的值，如果尝试用DET(别名(替换EXCLUDED，则错误相同

拜托，我做错了什么？？？

Answer 1

EXCLUDED总是指目标表的列名，而不是源。您也不能在分配的左侧使用目标表名称。

所以你需要

SET fecha = EXCLUDED.fecha,  
saldo = EXCLUDED.saldo