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 people (first_name text,	id int,	total int,	type text);
insert into people values
('Joe',	1234,	13,	'onsite'),
('Joe',	1234,	9,	'remote'),
('Jane',	4321,	99,	'remote'),
('Jane',	4321,	1,	'onsite')
  returning *;
--I want to query it and have the results returned like this:
/*
first_name	id	onsite	remote
Joe	1234	13	9
Jane	4321	1	99
*/

CREATE TABLE




first_name
id
total
type


Joe
1234
13
onsite

Joe
1234
9
remote

Jane
4321
99
remote

Jane
4321
1
onsite



INSERT 0 4


select first_name,
       id, 
       sum(total)filter(where type='onsite') as onsite,
       sum(total)filter(where type='remote') as remote
from people
group by first_name, id
order by first_name, id;


first_name
id
onsite
remote


Jane
4321
1
99

Joe
1234
13
9



SELECT 2


create extension tablefunc;
SELECT * FROM crosstab(
   $a$ select first_name, id, type, total from people order by 1,2,3 $a$ , 
   $b$ select distinct type from people order by 1 $b$
  ) AS ct(first_name text, id int, onsite int, remote int);

CREATE EXTENSION




first_name
id
onsite
remote


Jane
4321
1
99

Joe
1234
13
9



SELECT 2

first_name	id	total	type
Joe	1234	13	onsite
Joe	1234	9	remote
Jane	4321	99	remote
Jane	4321	1	onsite