Postgres insert is happening inspite of violating partial index constraint

Question

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

Answer 1

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)

Working fiddle