Hi we have a table named billing_infos , where a partial index is created on three fields,account_id, state_id and deleted_at where deleted_at is NULL ,
My expectation is insert operation should fail on trying to insert duplicate values for account_id, state_id, deleted_at = null .
But it seems it is creating another entry with duplicates. Is there any caveat with partial index when we check with null condition , I have gone through official documentation but couldn't find one, below is the snippet for my table schema
porterbizz=> \d+ application.billing_infos; Table
"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
Please remove deleted_at from your partial unique index because null never equals anything--not even another null.
Without the deleted_at, the uniqueness is enforced:
# 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.
But include deleted_at in the index, and the duplicate inserts succeed:
# 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)