Where clause in upsert conflict target

Suppose I have a table created like this:

CREATE TABLE actions (name text primary key, value text);
INSERT INTO actions (name) VALUES ('bla');

When attempting to insert a new row, there are three possibilties:

1. No row for ‘name’ exists. The new row should be inserted.
2. A row for ‘name’ exists that has a value of NULL. The existing row should be updated with the new value.
3. A row for ‘name ‘ exists that has a non-NULL value. The insert should fail.

I thought I should be able to use the UPSERT syntax like this:

INSERT INTO actions (name, value) values ('bla', 'val1')
ON CONFLICT(name) WHERE value IS NULL
DO UPDATE SET value = excluded.value;

INSERT INTO actions (name, value) values ('bla', 'val2')
ON CONFLICT(name) WHERE value IS NULL
DO UPDATE SET value = excluded.value;

The first command updates the existing row as expected. But the second command updates the row again, which I did not expect.

What is the where clause in the conflict target supposed to do? In my tests it doesn’t seem to matter if it evaluates to true or false.

Based on PostgreSQL Upsert with a WHERE clause I tried using a unique partial index instead of a primary key, but then I end up with multiple rows with the same name.

Answer

Using the second WHERE will do what you want:

INSERT INTO actions(name) VALUES ('bla');
INSERT INTO actions(name, value) VALUES ('bla', 'val1')
 ON CONFLICT(name) DO UPDATE SET value = excluded.value WHERE value IS NULL;
SELECT * FROM actions;
name        value
----------  ----------
bla         val1
INSERT INTO actions(name, value) VALUES ('bla', 'val2')
 ON CONFLICT(name) DO UPDATE SET value = excluded.value WHERE value IS NULL;
SELECT * FROM actions;
name        value
----------  ----------
bla         val1

---

The first WHERE, following the conflict column(s) is meant to select a particular partial index to use for detecting conflicts. An example to give you the feel for it:

sqlite> CREATE TABLE ex(name, value1, value2);
sqlite> CREATE UNIQUE INDEX ex_idx_name ON ex(name) WHERE value1 IS NULL;
sqlite> INSERT INTO ex(name) VALUES ('bla');
sqlite> INSERT INTO ex(name, value2) VALUES ('bla', 'val1');
Error: UNIQUE constraint failed: ex.name
sqlite> INSERT INTO ex(name, value1, value2) VALUES ('bla', 1, 'val2');
sqlite> SELECT * from ex;
name        value1      value2
----------  ----------  ----------
bla         (null)      (null)
bla         1           val2
sqlite> INSERT INTO ex(name, value2) VALUES ('bla', 'val3')
   ...> ON CONFLICT(name) WHERE value1 IS NULL DO UPDATE SET value2 = excluded.value2;
sqlite> SELECT * from ex;
name        value1      value2
----------  ----------  ----------
bla         (null)      val3
bla         1           val2
sqlite> INSERT INTO ex(name, value2) VALUES ('bla', 'val4')
   ...> ON CONFLICT(name) DO UPDATE SET value2 = excluded.value2;
Error: ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint

Note the last two INSERTs in particular: The first one updates the row with a null value, and the second one generates an error because it doesn’t fully specify a unique index.