Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

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;

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

CREATE INDEX

INSERT 0 1

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

create unique index idx_1 on billing_infos (account_id, state_id, deleted_at)
 where deleted_at is null;

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

CREATE INDEX

INSERT 0 1

INSERT 0 1