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 items (item_id serial, name varchar(10), item_group int);
create table items_ver (id serial, item_id int, name varchar(10), item_group int);
insert into items(name,item_group) values ('foo',10),('bar',10);
select * from items;


item_id
name
item_group


1
foo
10

2
bar
10



SELECT 2


insert into items_ver
select * from items where item_id=2;

ERROR:  column "item_id" is of type integer but expression is of type character varying
LINE 2: select * from items where item_id=2;
               ^
HINT:  You will need to rewrite or cast the expression.


insert into items_ver(item_id, name, item_group)
select * from items where item_id=2;

INSERT 0 1


insert into items_ver (item_id, name, item_group)
select item_id, name, item_group from items where item_id=2;

INSERT 0 1