Re: doc: Clarify what "excluded" represents for INSERT ON CONFLICT - Mailing list pgsql-hackers

From Robert Haas
Subject Re: doc: Clarify what "excluded" represents for INSERT ON CONFLICT
Date
Msg-id CA+TgmobiFHkGd8wGxs11qmiinL-ttvD1FeyHxhRfNU7EVVFOwQ@mail.gmail.com
Whole thread Raw
In response to Re: doc: Clarify what "excluded" represents for INSERT ON CONFLICT  (Peter Geoghegan <pg@bowt.ie>)
List pgsql-hackers
On Thu, Jun 30, 2022 at 5:05 PM Peter Geoghegan <pg@bowt.ie> wrote:
> On Thu, Jun 30, 2022 at 1:43 PM Robert Haas <robertmhaas@gmail.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



pgsql-hackers by date:

Previous
From: Dagfinn Ilmari Mannsåker
Date:
Subject: Re: [PATCH] Add result_types column to pg_prepared_statements view
Next
From: Joe Conway
Date:
Subject: Re: Hardening PostgreSQL via (optional) ban on local file system access