I have a table with a column where only 1 null is allowed (nulls should be considered unique).
I can a create partial index on this column to enforce this rule.
However I can't find a way to use that index in an ON CONFLICT clause.
How can I create a row with allocated_at=NULL or update the one existing row that has allocated_at=NULL?
CREATE TABLE test_table (
id serial PRIMARY KEY,
location text NOT NULL,
allocated_at timestamp UNIQUE
);
CREATE UNIQUE INDEX test_allocated_at_null ON test_table ((allocated_at IS NULL)) WHERE allocated_at IS NULL;
INSERT INTO test_table (location) values('my name');
INSERT INTO test_table (location)
values('my name2') ON CONFLICT (allocated_at)
DO UPDATE SET "location" = 'my name2';
-- ERROR: duplicate key value violates unique constraint "test_allocated_at_null"
-- DETAIL: Key ((allocated_at IS NULL))=(t) already exists.
INSERT INTO test_table (location)
values('my name2') ON CONFLICT ON CONSTRAINT test_allocated_at_null
DO UPDATE SET "location" = 'my name2';
-- ERROR: constraint "test_allocated_at_null" for table "test_table" does not exist