Error with Insert from View with ON Conflict
Hi,
I am trying to do an upsert using a view but for some reason get errors.
All works fine without the ON CONFLICT
INSERT INTO http_stats
SELECT * FROM view_http_stats AS V WHERE month =date_trunc('month',now())
ON CONFLICT (url,ip,month) DO UPDATE
SET last_update = now(),
checks = V.checks,
uptime = V.uptime,
errors = V.errors;
ERROR: 42P01: missing FROM-clause entry for table "v"
LINE 5: checks = V.checks,
^
LOCATION: errorMissingRTE, parse_relation.c:3460
Any help would be appreciated. Thanks in advance.
A
I'm pretty sure the 'alias' for the '.. on conflict do update ..' needs to
be 'excluded' (i.e. checks = excluded.checks, ...). Check the docs.
Steve
On Thu, Nov 4, 2021 at 8:05 AM Alex Magnum <magnum11200@gmail.com> wrote:
Show quoted text
Hi,
I am trying to do an upsert using a view but for some reason get errors.
All works fine without the ON CONFLICTINSERT INTO http_stats
SELECT * FROM view_http_stats AS V WHERE month =date_trunc('month',now())
ON CONFLICT (url,ip,month) DO UPDATE
SET last_update = now(),
checks = V.checks,
uptime = V.uptime,
errors = V.errors;ERROR: 42P01: missing FROM-clause entry for table "v"
LINE 5: checks = V.checks,
^
LOCATION: errorMissingRTE, parse_relation.c:3460Any help would be appreciated. Thanks in advance.
A
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.
--
Peter Geoghegan
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@bowt.ie> wrote:
Show quoted text
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.--
Peter Geoghegan