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?
