From: Tom Lane <tgl@sss.pgh.pa.us>
To: "David G. Johnston" <david.g.johnston@gmail.com>
cc: "digoal@126.com" <digoal@126.com>,
        "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #17845: insert into on conflict bug .
In-reply-to: 
 <CAKFQuwZtoQ_Mmt2xuEe7pFvnbGYzPM7i1swpaTq8Ew7jHqVukA@mail.gmail.com>
References: <17845-fee889ae7e226a76@postgresql.org>
 <CAKFQuwZtoQ_Mmt2xuEe7pFvnbGYzPM7i1swpaTq8Ew7jHqVukA@mail.gmail.com>
Comments: In-reply-to "David G. Johnston" <david.g.johnston@gmail.com>
	message dated "Thu, 16 Mar 2023 05:22:23 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <3759406.1678972226.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Thu, 16 Mar 2023 09:10:26 -0400
Message-ID: <3759407.1678972226@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3759407.1678972226%40sss.pgh.pa.us>
Precedence: bulk

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thursday, March 16, 2023, PG Bug reporting form <noreply@postgresql.org>
> wrote:
>> In the following insert statement, the row has not been updated multiple
>> times, why is it still showing an error?

>> insert into a
>> select * from (values (1,'a',date '2022-01-01'),(1,'b',date
>> '2022-01-02'),(1,'c',date '2022-01-03')) as t (id,info,ts) order by ts
>> desc
>> on conflict (id)
>> do update set info=excluded.info, ts=excluded.ts where a.ts < excluded.ts
>> ;

> You have id=1 in there three times which is precisely the definition of
> “multiple times” (I.e., more than 1)

Yeah.  I believe the reason for the restriction is that it would
otherwise be very uncertain what order the input rows get processed in.
(No, the "order by" won't save you, because there's still a join
to be done after that.)

			regards, tom lane