Postgres 9.6

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 t(id serial primary key, rnd double precision);

CREATE TABLE

with w as (insert into t(rnd) values(random()) returning *)
insert into t(rnd) select random() from w returning *;

id
rnd

2
0.713572988286614

INSERT 0 1

with w as (insert into t(rnd) values(random()) returning *)
insert into t(rnd) select random() from w returning *, 1.0 dummy;

id
rnd
dummy

4
0.677024329081178
1.0

INSERT 0 1

with w as (insert into t(rnd) values(random()) returning *)
insert into t(rnd) select random() from w returning *, w.rnd;

ERROR:  missing FROM-clause entry for table "w"
LINE 2: ...nsert into t(rnd) select random() from w returning *, w.rnd;
                                                                 ^

with w as (insert into t(rnd) values(random()) returning *),
     x AS (insert into t(rnd) select random() from w returning *)
SELECT w.rnd, x.rnd
  FROM w, x;

rnd
rnd

0.841886529233307
0.333116528112441

SELECT 1

SELECT * FROM t;

id
rnd

1
0.223235941492021

2
0.713572988286614

3
0.76253055408597

4
0.677024329081178

5
0.841886529233307

6
0.333116528112441

SELECT 6

id	rnd
1	0.223235941492021
2	0.713572988286614
3	0.76253055408597
4	0.677024329081178
5	0.841886529233307
6	0.333116528112441