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 wrote:
> On Wed, Nov 3, 2021 at 2:18 PM Steve Baldwin
> 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
>
Re: Error with Insert from View with ON Conflict
On Wed, Nov 3, 2021 at 2:18 PM Steve Baldwin 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
Re: Error with Insert from View with ON Conflict
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 wrote:
> 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
>
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
