1

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?

1 Answer 1

2

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.