On Fri, Apr 23, 2021 at 10:32:31AM -0300, Diego wrote:
>
> ERROR: column excluded.fec does not exist LINE 25: ...ON CONFLICT (address)
> DO UPDATE SET saldo.fecha = EXCLUDED.f... ^ SQL state: 42703 Character: 1052
>
[...]
> 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
>
> ;
>
No veo donde están definidos los campos "fec" e "imp". hasta donde
entiendo no existen y por eso postgres te dice que no existen.
La calusula ON CONFLICT debería quedar así:
ON CONFLICT (address) DO UPDATE SET saldo.fecha = EXCLUDED.fecha,
saldo.saldo = EXCLUDED.saldo
--
Jaime Casanova
Director de Servicios Profesionales
SystemGuards - Consultores de PostgreSQL
