PostgreSQL ON CONFLICT with a WHERE clause

Question

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.

Answer 1

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.