Re: Problema con insert desde select y on conflict

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