主页 / coding / 问题 / 76919679
Accepted
lazarus
Asked: 2023-08-17 16:23:42 +0800 CST

尽管违反了部分索引约束，Postgres 插入仍在发生

  • 772

account_id您好，我们有一个名为 billing_infos 的表，其中在三个字段、state_id和上创建了部分索引，deleted_at其中deleted_at是NULL，我的期望是插入操作在尝试为account_id、state_id、插入重复值时应该失败deleted_at = null。但它似乎正在创建另一个具有重复项的条目。当我们检查空条件时，部分索引是否有任何警告，我已经浏览了官方文档但找不到，下面是我的表架构的片段

porterbizz=> \d+ application.billing_infos; 桌子

"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 个回答

  1. Best Answer

    请从您的部分唯一索引中删除deleted_at，因为null永远不等于任何东西——甚至不等于另一个null。

    如果没有deleted_at，则会强制执行唯一性：

    # 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.

    但包含deleted_at在索引中，重复插入会成功：

    # 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)

    工作小提琴

    • 1

相关问题