Re: BUG #16768: PostgreSQL 12.5 - INSERT ON CONFLICT succeeds on data INSERT but does nothing on UPDATE

From: Peter Geoghegan <pg(at)bowt(dot)ie>
To: theqacollective(at)gmail(dot)com, PostgreSQL mailing lists <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: Re: BUG #16768: PostgreSQL 12.5 - INSERT ON CONFLICT succeeds on data INSERT but does nothing on UPDATE
Date: 2020-12-09 01:14:27
Message-ID: CAH2-WznB6FZCQG2F3hiBvPFkfYuGrvhuH0qDjRCJWWMuQ8k7gg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

On Tue, Dec 8, 2020 at 5:02 PM PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
> The SQL below recreates my issue. That is, the LAST insert statement DOES
> NOT update the accountinfo_test table value for 'balance' to 200.0 ... it
> remains at the previous value of 100.0.

I think that you need to UPDATE using the excluded.* pseudo table. For
example, "SET my_col = excluded.my_col". As things stand you're using
the target table itself. Technically the row is being updated, but
with the wrong values (the existing values rather than your intended
new successor values).

--
Peter Geoghegan

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message QA Collective 2020-12-09 02:25:45 Re: BUG #16768: PostgreSQL 12.5 - INSERT ON CONFLICT succeeds on data INSERT but does nothing on UPDATE
Previous Message PG Bug reporting form 2020-12-09 00:25:26 BUG #16768: PostgreSQL 12.5 - INSERT ON CONFLICT succeeds on data INSERT but does nothing on UPDATE