From: Tom Lane <tgl@sss.pgh.pa.us>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: digoal@126.com <digoal@126.com>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #17845: insert into on conflict bug .
Date: Thu, 16 Mar 2023 09:10:26 -0400
Message-ID: <3759407.1678972226@sss.pgh.pa.us> (raw)
In-Reply-To: <CAKFQuwZtoQ_Mmt2xuEe7pFvnbGYzPM7i1swpaTq8Ew7jHqVukA@mail.gmail.com>
References: <17845-fee889ae7e226a76@postgresql.org>
<CAKFQuwZtoQ_Mmt2xuEe7pFvnbGYzPM7i1swpaTq8Ew7jHqVukA@mail.gmail.com>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, david.g.johnston@gmail.com, digoal@126.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17845: insert into on conflict bug .
In-Reply-To: <3759407.1678972226@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox