0

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

Jake
  • 101
  • 1

0 Answers0