On Sat, Sep 24, 2022 at 8:49 AM Barry Kimelman <blkimelman@xxxxxxxxx> 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.
Which seems sufficiently clearly written.
David J.
