Postgres 16

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 test (
  id int generated by default as identity primary key, 
  id2 smallserial unique not null,
  firstname text, 
  surname text
  );

CREATE TABLE


select firstname, surname, count(*)
from test
group by id;--that's the PK


firstname
surname
count



SELECT 0


select firstname, surname, count(*)
from test
group by id2;--that's just a `unique not null`

ERROR:  column "test.firstname" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select firstname, surname, count(*)
               ^


insert into test(firstname,surname) values
('f1','s1'),
('f1','s1') returning *;


id
id2
firstname
surname


1
1
f1
s1

2
2
f1
s1



INSERT 0 2


select firstname, surname, count(*)
from test
group by id;--that's the PK


firstname
surname
count


f1
s1
1

f1
s1
1



SELECT 2


select firstname, surname, count(*)
from test
group by id2;--that's just a `unique not null`

ERROR:  column "test.firstname" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select firstname, surname, count(*)
               ^


create table test2 (
  id int generated by default as identity primary key, 
  fk int references test(id),
  other_info text
  );
insert into test2 (fk,other_info)values
(1,'other_info_1_1'),
(1,'other_info_1_2'),
(2,'other_info_2_3') returning *

CREATE TABLE




id
fk
other_info


1
1
other_info_1_1

2
1
other_info_1_2

3
2
other_info_2_3



INSERT 0 3


select firstname, surname, count(*)
from test inner join test2 on test.id=test2.fk
group by test.id;--that's the PK


firstname
surname
count


f1
s1
1

f1
s1
2



SELECT 2


select firstname, surname,
       other_info, --that's from another table, so it won't work
       count(*)
from test inner join test2 on test.id=test2.fk
group by test.id;--that's the PK

ERROR:  column "test2.other_info" must appear in the GROUP BY clause or be used in an aggregate function
LINE 2:        other_info, --that's from another table, so it won't ...
               ^