15

Postgres documentation makes it seem like a WHERE clause is possible as an ON CONFLICT condition: https://www.postgresql.org/docs/9.5/static/sql-insert.html

I have not been able to get this working (if it's possible). Here is one of the many permutations I've tried:

INSERT INTO friends (id, dob, frn, status, "groupId", 
"createdAt", "updatedAt") 
VALUES ('1da04305-68ef-4dc1-be6c-
826ab83a6479', '1937-06-01T08:29:08-07:00', 100001, 'New', 'bc1567bc-
14ff-4ba2-b108-4cb2e0f0f768', NOW(), NOW()) 
ON CONFLICT 
    WHERE frn=100001 DO NOTHING

frn does not have any constraints, so the simpler syntax:

ON CONFLICT (frn) DO NOTHING

throws database errors. My hope is this is a simple syntax issue.

Cœur
  • 37,241
  • 25
  • 195
  • 267
smeckydev
  • 433
  • 2
  • 4
  • 13
  • what are you trying to achieve? uniqueness on `frn`? because in that case, you should have a unique constraint on `frn`. – pozs Jun 15 '17 at 16:03
  • frn is only unique within the context of a groupId. So the combination of a frn and a groupId are unique, but in isolation, a frn is not unique. – smeckydev Jun 15 '17 at 16:07
  • then, with the new `UPSERT` you can only do `ON CONFLICT ("groupId", frn)`. -- if you want to check only `frn`: there is no high-concurrency safe solution for that. – pozs Jun 15 '17 at 16:11
  • Yes, I think that would work if I added an exclusion constraint on the table. I'm still curious if a WHERE clause in the new UPSERT possible? – smeckydev Jun 15 '17 at 16:27

1 Answers1

30

The WHERE clause is subordinate to the ON CONFLICT (constraint) DO UPDATE SET ... clause. It only looks at the single row that violated the specified constraint when trying to INSERT.

A syntactically valid example:

INSERT INTO friends (
    id, 
    dob, frn, status,
    "groupId", "createdAt", "updatedAt"
) VALUES (
    '1da04305-68ef-4dc1-be6c-826ab83a6479',
    '1937-06-01T08:29:08-07:00', 100001, 'New',
    'bc1567bc-14ff-4ba2-b108-4cb2e0f0f768', NOW(), NOW()
) 
ON CONFLICT ("groupId", frn) DO UPDATE SET
    status='Revised', 
    "updatedAt"=NOW()
WHERE friends.status<>'Deleted';

Or as an operational example.

gwaigh
  • 1,182
  • 1
  • 11
  • 23
  • 9
    Note, you probably want to write it as `WHERE friends.status<>'Deleted'`, [as otherwise](https://stackoverflow.com/questions/36378646/on-insert-column-reference-score-is-ambiguous) you'll most likely get a `column reference "status" is ambiguous` error. – Klesun Nov 22 '21 at 12:17
  • 1
    @Klesun, I am so glad you added a comment to this ancient thread, and just in time to solve my problem! – AlastairG Nov 24 '21 at 14:33
  • @Klesun what happens if where condition is false? 'DO NOTHING' vs 'duplicate key violation' – valijon Jan 31 '23 at 15:20
  • 1
    @valijon it will be 'DO NOTHING'. – Klesun Feb 01 '23 at 18:23