Início / user-103715

Brendan Maguire's questions

Martin Hope
Brendan Maguire
Asked: 2023-10-17 21:41:24 +0800 CST
  • 6

Dada esta definição de tabela com um índice parcial:

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;

Gostaria de garantir que haja apenas um valor onde (user_id, item_id)o registro não foi arquivado. Esta restrição não deve ser aplicada a registros com archived_atvalor não nulo (ou seja, devemos permitir muitos registros arquivados para um determinado (user_id, item_id)emparelhamento).

A restrição acima funciona conforme planejado quando user_ide item_idsão especificados:

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;

Dá o seguinte erro:

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

Mas permite vários registros com line_idof 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;

Saída:

BEGIN
INSERT 0 1
INSERT 0 1
COMMIT

Também tentei com um índice exclusivo com nulos não distintos assim:

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;

Mas é claro que isso não leva em conta o archived_atvalor:

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;

E recebo este erro indesejado:

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

Existe uma maneira de proibir múltiplas entradas para (user_id, item_id)quando archived_atfor, NULLmas permitir quando archived_atnão for NULL?

postgresql