On Thu, Jun 30, 2022 at 5:05 PM Peter Geoghegan <p...@bowt.ie> wrote: > On Thu, Jun 30, 2022 at 1:43 PM Robert Haas <robertmh...@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
