Document INSERT .. ON CONFLICT () WHERE ..

[mgartner]

Marcus Gartner (mgartner) commented:

I don't see any mention of this form of INSERT in our documentation. It has some peculiar behavior that we should make clear (see cockroachdb/cockroach#119117).

Here's some examples of how it is used:

CREATE TABLE t (
  k INT PRIMARY KEY,
  i INT,
  j INT,
  UNIQUE INDEX (i) WHERE j > 0
);
-- CREATE TABLE

INSERT INTO t VALUES (1, 10, 100);
-- INSERT 0 1

-- Without the WHERE clause, a valid arbiter to detect the conflict cannot be
-- found, so the statement errors.
INSERT INTO t VALUES (2, 10, 200) ON CONFLICT (i) DO UPDATE SET i = 30;
-- ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
-- SQLSTATE: 42P10

-- Adding the proper WHERE clause allows the statement to succeed.
INSERT INTO t VALUES (2, 10, 200) ON CONFLICT (i) WHERE j > 0 DO UPDATE SET i = 30;
-- INSERT 0 1

SELECT * FROM t;
--   k | i  |  j
-- ----+----+------
--   1 | 30 | 100
-- (1 row)

-- The WHERE clause does not need to match the partial index predicate exactly.
-- It only needs to _imply_ the partial idnex predicate.
INSERT INTO t VALUES (3, 30, 300) ON CONFLICT (i) WHERE j > 10 DO UPDATE SET i = 40;
-- INSERT 0 1

SELECT * FROM t;
--   k | i  |  j
-- ----+----+------
--   1 | 40 | 100
-- (2 rows)

Jira Issue: DOC-9665