Postgres 12

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 address (address_id integer primary key, user_id integer, is_default boolean);
create unique index only_one_default_address 
    on address (user_id) 
where is_default;
create or replace function reset_default_address()
  returns trigger
as
$$
begin  
  if new.is_default then 
     update address
       set is_default = false
     where user_id = new.user_id;
  end if;
  return new;
end;  
$$
language plpgsql;

create trigger trg_change_default_address
  before update or insert on address
  for each row
  execute procedure reset_default_address();
  
-- one default, one non-default address for user_id = 1
insert into address (address_id, user_id, is_default) values (1, 1, true);
insert into address (address_id, user_id, is_default) values (2, 1, false); -- no problem

select *
from address;


1 rows affected

1 rows affected



address_id
user_id
is_default


1
1
t

2
1
f


-- this will reset the default flag for the first address
insert into address (address_id, user_id, is_default) values (3, 1, true);

select *
from address;
1 rows affected



address_id
user_id
is_default


1
1
f

2
1
f

3
1
t