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@bowt.ie> wrote:
On Wed, Nov 3, 2021 at 2:18 PM Steve Baldwin <steve.baldwin@gmail.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.
