Início / coding / Perguntas / 76919679
Accepted
lazarus
Asked: 2023-08-17 16:23:42 +0800 CST

A inserção do Postgres está acontecendo apesar de violar a restrição de índice parcial

  • 772

Olá, temos uma tabela chamada billing_infos , onde um índice parcial é criado em três campos, , account_ide state_idonde deleted_atestá deleted_at, NULLMinha expectativa é que a operação de inserção falhe ao tentar inserir valores duplicados para account_id, state_id, deleted_at = null. Mas parece que está criando outra entrada com duplicatas. Existe alguma advertência com índice parcial quando verificamos com condição nula, examinei a documentação oficial , mas não consegui encontrar uma, abaixo está o trecho do meu esquema de tabela

porterbizz=> \d+ application.billing_infos; Mesa

"application.billing_infos"
     Column      |            Type             | Collation | Nullable |                        Default                        | Storage  | Stats target | Description
-----------------+-----------------------------+-----------+----------+-------------------------------------------------------+----------+--------------+-------------
 id              | integer                     |           | not null | nextval('application.billing_infos_id_seq'::regclass) | plain    |              |
 account_id      | integer                     |           | not null |                                                       | plain    |              |
 gst_in          | character varying(256)      |           | not null |                                                       | extended |              |
 gst_reg_address | character varying(256)      |           | not null |                                                       | extended |              |
 invoice_address | character varying(256)      |           | not null |                                                       | extended |              |
 state_id        | integer                     |           | not null |                                                       | plain    |              |
 default         | boolean                     |           | not null | false                                                 | plain    |              |
 deleted_at      | timestamp without time zone |           |          |                                                       | plain    |              |
Indexes:
    "billing_infos_pkey" PRIMARY KEY, btree (id)
    "account_id_state_id_deleted_at_uniq_index" UNIQUE, btree (account_id, state_id, deleted_at) WHERE deleted_at IS NULL
postgresql

1 respostas

  1. Best Answer

    Remova deleted_atde seu índice exclusivo parcial porque nullnunca é igual a nada, nem mesmo a outro null.

    Sem o deleted_at, a exclusividade é aplicada:

    # create table billing_infos (
   id int not null generated always as identity primary key,
   account_id int not null,
   state_id int not null,
   deleted_at timestamp
);
CREATE TABLE
 
# create unique index idx_1 on billing_infos (account_id, state_id) 
   where deleted_at is null;
CREATE INDEX
 
# insert into billing_infos(account_id, state_id, deleted_at) values (1, 1, null);
INSERT 0 1

# insert into billing_infos(account_id, state_id, deleted_at) values (1, 1, null);

ERROR:  duplicate key value violates unique constraint "idx_1"
DETAIL:  Key (account_id, state_id)=(1, 1) already exists.

    Mas inclua deleted_atno índice e as inserções duplicadas são bem-sucedidas:

    # drop index idx_1;
DROP INDEX
# 
# create unique index idx_1 on billing_infos (account_id, state_id, deleted_at)
   where deleted_at is null;
CREATE INDEX
 
# insert into billing_infos(account_id, state_id, deleted_at) values (1, 1, null);
INSERT 0 1
 
# select * from billing_infos;
┌────┬────────────┬──────────┬────────────┐
│ id │ account_id │ state_id │ deleted_at │
├────┼────────────┼──────────┼────────────┤
│  1 │          1 │        1 │            │
│  3 │          1 │        1 │            │
└────┴────────────┴──────────┴────────────┘
(2 rows)

    Violino de trabalho

    • 1

relate perguntas