Buenas buenas, como va?
Hoy vengo con una consulta sobre on conflict en un insert. Asi como la
ven la consulta funciona si en el on conflict le ponen un nothing, pero
cuando deseo cambiarla por un update dice:
ERROR: column excluded.fec does not exist LINE 25: ...ON CONFLICT
(address) DO UPDATE SET saldo.fecha = EXCLUDED.f... ^ SQL state: 42703
Character: 1052
entendi que quizas no reconocia el alias y le puse NEW, OLD, det (el del
select) y falla siempre. Pero ahora ya se me acabaron las ideas.
Alguien me podra orientar?
INSERT INTO wallet.saldo(address, fecha, saldo)
select '0xaa515035c04aab' as token_address,
max(block_timestamp)::date , sum(importe)
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
;
Mil gracias!
