From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Rowley <dgrowleyml@gmail.com>
cc: Tim Starling <tstarling@wikimedia.org>, pgsql-general@lists.postgresql.org
Subject: Re: Upsert error "column reference is ambiguous"
In-reply-to: <CAApHDvq-EUKOm2rSFoctDoXYdV-rJis9y9GcmsBiqcO7DbaGpQ@mail.gmail.com>
References: <a31a76b6-b176-47fe-8778-9dfece231341@wikimedia.org> <697017.1745837694@sss.pgh.pa.us> <80f95342-cc09-4236-a2d7-68538fbfc41b@wikimedia.org> <774865.1745848449@sss.pgh.pa.us> <CAApHDvq-EUKOm2rSFoctDoXYdV-rJis9y9GcmsBiqcO7DbaGpQ@mail.gmail.com>
Comments: In-reply-to David Rowley <dgrowleyml@gmail.com>
	message dated "Wed, 30 Apr 2025 00:43:17 +1200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1026715.1745938318.1@sss.pgh.pa.us>
Date: Tue, 29 Apr 2025 10:51:58 -0400
Message-ID: <1026716.1745938318@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/1026716.1745938318%40sss.pgh.pa.us>
Precedence: bulk

David Rowley <dgrowleyml@gmail.com> writes:
> Another thought is that you can have an UPDATE with a RETURNING
> clause. An unqualified column defaults to NEW even though you could
> argue it's ambiguous due to OLD (as of 80feb727c). Likely we were
> forced into making it work that way through not wanting to force
> everyone to rewrite their RETURNING statements when upgrading to v18.
> The moral of that story is, UPDATE isn't exactly consistent already
> about when it requires column qualifications. Maybe it's weird to
> insist that users qualify columns with their ON CONFLICT UPDATE SET
> when RETURNING is happy to assume you must have meant NEW.

That's an analogy I hadn't thought of, and it does seem on-point.
You might be right that we would not have done it like that if
we'd invented RETURNING's ability to support "OLD" at the get-go.
Nonetheless, it's there now and is a pretty similar precedent.

			regards, tom lane