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