From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Peter Geoghegan <pg(at)bowt(dot)ie> |
Cc: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: doc: Clarify what "excluded" represents for INSERT ON CONFLICT |
Date: | 2022-07-01 12:30:39 |
Message-ID: | CA+TgmobiFHkGd8wGxs11qmiinL-ttvD1FeyHxhRfNU7EVVFOwQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Thu, Jun 30, 2022 at 5:05 PM Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
> On Thu, Jun 30, 2022 at 1:43 PM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> > rhaas=# insert into foo values (1, 'frob') on conflict (a) do update
> > set b = (select b || 'nitz' from excluded);
> > ERROR: relation "excluded" does not exist
> > LINE 1: ...ct (a) do update set b = (select b || 'nitz' from excluded);
> >
> > I do find that a bit of a curious error message, because that relation
> > clearly DOES exist in the range table.
>
> Let's say that we supported this syntax. I see some problems with that
> (you may have thought of these already). Thinking of "excluded" as a
> separate table creates some very thorny questions, such as:
>
> How would the user be expected to handle the case where there was more
> than a single "row" in "excluded"? How could the implementation know
> what the contents of the "excluded table" were ahead of time in the
> multi-row-insert case? We'd have to know about *all* of the conflicts
> for all rows proposed for insertion to do this, which seems impossible
> in the general case -- because some of those conflicts won't have
> happened yet, and cannot be predicted.
I was assuming it would just behave like a 1-row table i.e. these
would do the same thing:
insert into foo values (1, 'frob') on conflict (a) do update set b =
(select excluded.b || 'nitz');
insert into foo values (1, 'frob') on conflict (a) do update set b =
(select b || 'nitz' from excluded);
I'm actually kinda unsure why that doesn't already work. There may
well be a very good reason, but my naive thought would be that if
excluded doesn't have a range table entry, the first one would fail
because excluded can't be looked up in the range table, and if it does
have a range-table entry, then the second one would work because the
from-clause reference would find it just like the qualified column
reference did.
--
Robert Haas
EDB: http://www.enterprisedb.com
From | Date | Subject | |
---|---|---|---|
Next Message | Joe Conway | 2022-07-01 12:46:05 | Re: Hardening PostgreSQL via (optional) ban on local file system access |
Previous Message | Dagfinn Ilmari Mannsåker | 2022-07-01 12:27:20 | Re: [PATCH] Add result_types column to pg_prepared_statements view |