Postgres 10

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 a (id int, user_id int, f_id int);

create table b (id int, f_id int);

create unique index A_user_id_key on a(user_id);

CREATE TABLE

CREATE TABLE

CREATE INDEX

insert into a 
values (1, 1, 11),
       (2, 3, 22),
       (3, 2, 33),
       (4, 5, 44 );

INSERT 0 4

insert into b
values (1, 11),
       (3, 22),
       (3, 33),
       (4, 44);

INSERT 0 4

update a
set    user_id = b.id
from   b
where  a.f_id = b.f_id;

ERROR:  duplicate key value violates unique constraint "a_user_id_key"
DETAIL:  Key (user_id)=(3) already exists.

update a
set    user_id = b.id
from   b
where  a.f_id = b.f_id
and    not exists(select 1 from a where a.user_id = b.id);

UPDATE 1

select * from a;

id
user_id
f_id

1
1
11

2
3
22

3
2
33

4
4
44

SELECT 4

select a.*
from   a
join   b
on     a.f_id = b.f_id
and    exists(select 1 from a where user_id = b.id and f_id <> b.f_id);

id
user_id
f_id

3
2
33

SELECT 1