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.
The "excluded" pseudo-table is conceptually similar to an from_item
alias used within an UPDATE .... FROM ... where the target table is
also the from_item table (i.e. there is a self-join). There is only
one table involved.
--
Peter Geoghegan