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

select version();


version


PostgreSQL 14.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit


create table users ( id          integer generated always as identity
                   , name        text
                   , assignedid  integer
                   , constraint  assigned_uk unique (assignedid) 
                   ) ;                  

create sequence user_assigned_seq;

insert into users(name) 
  values ('User_A'), ('User_B'), ('User_C'); 
   
select * from users;
3 rows affected



id
name
assignedid


1
User_A
null

2
User_B
null

3
User_C
null


  
insert into users(name, assignedid )
  select 'User_A2',  nextval('user_assigned_seq');
 
select * from users;
1 rows affected



id
name
assignedid


1
User_A
null

2
User_B
null

3
User_C
null

4
User_A2
1


update users 
   set assignedid = nextval('user_assigned_seq')
 where id = 2; 

select * from users;
1 rows affected



id
name
assignedid


1
User_A
null

3
User_C
null

4
User_A2
1

2
User_B
2


 update users 
   set assignedid = 1
 where id = 1;  
ERROR:  duplicate key value violates unique constraint "assigned_uk"
DETAIL:  Key (assignedid)=(1) already exists.