Postgres - Is it possible to have a `UNIQUE INDEX` with `NULLS NOT DISTINCT`?

Question

Given this table definition with a partial index:

BEGIN;
CREATE TABLE user_note (
 user_id VARCHAR NOT NULL,
 item_id VARCHAR,
 note VARCHAR NOT NULL,
 archived_at TIMESTAMP
);
CREATE UNIQUE INDEX active_note
 ON user_note(user_id, item_id)
 WHERE archived_at IS NULL;
END;

I would like to ensure there is only one value of (user_id, item_id) where the record has not been archived. This constraint should not apply to records with archived_at as a non null value (i.e. we should allow many archived records for a given (user_id, item_id) pairing).

The above constraint works as intended when user_id and item_id are both specified:

BEGIN;
INSERT INTO user_note (user_id, item_id, note) VALUES ('user_1', 'item_1', 'A general note');
INSERT INTO user_note (user_id, item_id, note) VALUES ('user_1', 'item_1', 'A different note');
END;

Gives the following error:

BEGIN
INSERT 0 1
ERROR: duplicate key value violates unique constraint "active_note"
DETAIL: Key (user_id, item_id)=(user_1, item_1) already exists.
make: *** [populate] Error 1

But allows multiple records with line_id of NULL:

BEGIN;
INSERT INTO user_note (user_id, note) VALUES ('user_1', 'A general note');
INSERT INTO user_note (user_id, note) VALUES ('user_1', 'A different note');
END;

Output:

BEGIN
INSERT 0 1
INSERT 0 1
COMMIT

I have also tried with a unique index with nulls not distinct like so:

BEGIN;
CREATE TABLE user_note (
 user_id VARCHAR NOT NULL,
 item_id VARCHAR,
 note VARCHAR NOT NULL,
 archived_at TIMESTAMP,
 UNIQUE NULLS NOT DISTINCT (user_id, item_id)
);
END;

But this of course does not take into account the archived_at value:

BEGIN;
INSERT INTO user_note (user_id, note, archived_at) VALUES ('user_1', 'A general note', CURRENT_TIMESTAMP);
INSERT INTO user_note (user_id, note, archived_at) VALUES ('user_1', 'A different note', CURRENT_TIMESTAMP);
END;

And I get this unwanted error:

BEGIN
INSERT 0 1
ERROR: duplicate key value violates unique constraint "user_note_user_id_item_id_key"
DETAIL: Key (user_id, item_id)=(user_1, null) already exists.
make: *** [populate] Error 1

Is there a way to disallow multiple entries for (user_id, item_id) when archived_at is NULL but to allow when archived_at is not NULL?

Answer 1

Identify a string that cannot occur in item_id, for example '@@@'. Then create your unique index like this:

CREATE UNIQUE INDEX ON user_note (
 user_id,
 coalesce(item_id, '@@@')
) WHERE archived_at IS NULL;