Обсуждение: 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
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:
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_statsSELECT * FROM view_http_stats AS V WHERE month =date_trunc('month',now())ON CONFLICT (url,ip,month) DO UPDATESET 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.
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())
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.
--
Peter Geoghegan