Re: problem with on conflict / do update using psql 14.4

On Sat, Sep 24, 2022 at 8:49 AM Barry Kimelman <blkimelman(at)gmail(dot)com> wrote:

>
> I thought the whole point of ON CONFLICT DO UPDATE was so that you could
> modify the data so that it would be inserted
>

Nope, the words "DO UPDATE" mean "DO an UPDATE command instead of failing
for the CONFLICTing INSERT command". As mentioned, if you want to ensure
you perform an insert of a new record the data you are inserting must not
conflict with existing data. You must do that prior to executing the
command.

Or, in the words of the documentation:

The optional ON CONFLICT clause specifies an alternative action to raising
a unique violation or exclusion constraint violation error.
...
ON CONFLICT DO UPDATE updates the existing row that conflicts with the row
proposed for insertion as its alternative action.

https://www.postgresql.org/docs/current/sql-insert.html

Which seems sufficiently clearly written.

David J.