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

On Thu, Jun 9, 2022 at 11:40 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>     As one cannot place excluded in a FROM clause (subquery) in the
>     ON CONFLICT clause referring to it as a table, ...

Well, it would be nice if you had included a test case rather than
leaving it to the reviewer or committer to construct one.  In general,
dropping subtle patches with minimal commentary isn't really very
helpful.

But I decided to dig in and see what I could figure out. I constructed
this test case first, which does work:

rhaas=# create table foo (a int primary key, b text);
CREATE TABLE
rhaas=# insert into foo values (1, 'blarg');
INSERT 0 1
rhaas=# insert into foo values (1, 'frob') on conflict (a) do update
set b = (select excluded.b || 'nitz');
INSERT 0 1
rhaas=# select * from foo;
 a |    b
---+----------
 1 | frobnitz
(1 row)

Initially I thought that was the case you were talking about, but
after staring at your email for another 20 minutes, I figured out that
you're probably talking about something more like this, which doesn't
work:

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. I know that because, if I use a
wrong column name, I get a complaint about the column not existing,
not the relation not existing:

rhaas=# insert into foo values (1, 'frob') on conflict (a) do update
set b = (select excluded.bbbbbbbbb || 'nitz');
ERROR:  column excluded.bbbbbbbbb does not exist
LINE 1: ...'frob') on conflict (a) do update set b = (select excluded.b...

That said, I am not convinced that changing the documentation in this
way is a good idea. It is clear that, at the level of the code,
"excluded" behaves like a pseudo-table, and the fact that it isn't
equivalent to a real table in all ways, or that it can't be referenced
at every point in the query equally, doesn't change that. I don't
think that the language you're proposing is horrible or anything --
the distinction between a special table and a special name that
behaves somewhat like a single-row table is subtle at best -- but I
think that the threshold to commit a patch like this is that the
change has to be a clear improvement, and I don't think it is.

I think it might be fruitful to consider whether some of the error
messages here could be improved or even whether some of the
non-working cases could be made to work, but I'm just not really
seeing the value of tinkering with documentation which is, in my view,
not wrong.

-- 
Robert Haas
EDB: http://www.enterprisedb.com