Re: Error with Insert from View with ON Conflict

Steve, Peter,
thanks .

Below query works. the view is used to generate the stats and not using the
table http_ping_uptime_stats . So my interpretation was that I need to
alias the view for the UPDATE to know where the value is coming from as
both have the same name.
That obviously did not work. I also tried to give different names the
fields returned in the view eg. checks2, uptime2 etc... so that there
won't be a conflict but SET checks = V.checks2 or checks = checks2 also
did not work.

All works now as intended. Thanks for the hint!

Alex

INSERT INTO http_ping_uptime_stats
SELECT * FROM view_http_ping_uptime_stats AS V WHERE month
=date_trunc('month',now())
ON CONFLICT (url,ip,month) DO UPDATE
SET last_update = excluded.last_update,
checks = excluded.checks,
uptime = excluded.uptime,
errors = excluded.errors;

On Thu, Nov 4, 2021 at 8:54 AM Peter Geoghegan <pg(at)bowt(dot)ie> wrote:

> On Wed, Nov 3, 2021 at 2:18 PM Steve Baldwin <steve(dot)baldwin(at)gmail(dot)com>
> wrote:
> > I'm pretty sure the 'alias' for the '.. on conflict do update ..' needs
> to be 'excluded' (i.e. checks = excluded.checks, ...). Check the docs.
>
> That's right . The excluded.* pseudo-table isn't exactly the same
> thing as the target table -- it is a tuple that has the same "shape",
> that represents what the implementation tried (and failed) to insert
> into the table. I have to imagine that Alex wants to reference that,
> because that's the standard idiomatic approach with ON CONFLICT. And
> because the only alternative interpretation is that Alex intends to
> update those columns using their current values (not new values),
> which won't really change anything -- that seems unlikely to have been
> the intent.
>
> --
> Peter Geoghegan
>