Postgres 15

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 users(id int primary key);
insert into users values (1),(2) returning *;

create table test2(
  id int primary key,
  userid int not null references users(id), 
  misc text);

insert into test2 (id,userid,misc) values 
(1, 1, 'a'),
(2, 1, 'b'),
(3, 1, 'c')
returning *;

create table true_row(
  fk_userid int not null references users(id) unique,
  fk_test2_id int not null references test2(id) );
insert into true_row (fk_userid,fk_test2_id) values (1,3);

select a.*,
       b.fk_test2_id is not null as "isactive"
from test2 a left join true_row b on a.id=b.fk_test2_id
order by a.id;

CREATE TABLE

id

1

2

INSERT 0 2

CREATE TABLE

id
userid
misc

1
1
a

2
1
b

3
1
c

INSERT 0 3

CREATE TABLE

INSERT 0 1

id
userid
misc
isactive

1
1
a
f

2
1
b
f

3
1
c
t

SELECT 3

insert into true_row (fk_userid,fk_test2_id) values (1,2);

ERROR:  duplicate key value violates unique constraint "true_row_fk_userid_key"
DETAIL:  Key (fk_userid)=(1) already exists.

insert into true_row (fk_userid,fk_test2_id) values (2,1);

INSERT 0 1