Postgres 13

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 IF NOT EXISTS company_representatives (
 _id          integer NOT NULL,
 name         varchar(50) NOT NULL,
 surname      varchar(100) NOT NULL,
 date_of_join date NOT NULL,
 role         varchar(250) NOT NULL,
 company_id   integer NOT NULL
);

INSERT INTO company_representatives VALUES
(1,'random name','random surname', '2001-01-23', 'CEO', 1),
(2,'next random name','next random surname', '2001-01-23', 'Co-founder', 1),
(3,'John','Doe', '2003-02-12', 'HR', 1),
(4,'Bread','Pitt', '2001-01-23', 'Security officer', 1),
(5,'Toast','Malone', '1997-11-05', 'CEO', 2),
(6,'HR company 2','Mustermann', '1997-11-05', 'HR', 2),
(7,'Co-founder company 2','next random surname 2', '2001-01-23', 'Co-founder', 2),
(8,'Security officer company 2','next random surname 3', '2001-01-23', 'Security officer', 2);


8 rows affected

SELECT *
FROM crosstab(
 'SELECT company_id,  _id, name::text
  FROM company_representatives ORDER BY company_id,role' 
) AS ct(company_id integer, ceo text,co_founder text,hr text,security_officer text);


company_id
ceo
co_founder
hr
security_officer


1
random name
next random name
John
Bread

2
Toast
Co-founder company 2
HR company 2
Security officer company 2


SELECT *
FROM crosstab(
 'SELECT company_id,  _id, _id
  FROM company_representatives ORDER BY company_id,role' 
) AS ct(company_id integer, ceo int,co_founder int,hr int,security_officer int);


company_id
ceo
co_founder
hr
security_officer


1
1
2
3
4

2
5
7
6
8

company_id	ceo	co_founder	hr	security_officer
1	random name	next random name	John	Bread
2	Toast	Co-founder company 2	HR company 2	Security officer company 2