Postgres 11

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?.

select version();


version


PostgreSQL 11.20 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-18), 64-bit



SELECT 1


CREATE TABLE "locations" (
    "code" varchar(10),
    "location_name" varchar(50)
);

INSERT INTO "locations" ("code", "location_name") VALUES ('KL', 'Kuala Lumpur'),
('SG', 'Singapore'),
('JKR', 'Jakarta');

CREATE TABLE



INSERT 0 3


CREATE TABLE "payments" (
    "location_code" varchar(10),
    "amount" float8
);

INSERT INTO "payments" ("location_code", "amount") VALUES ('KL', '50'),
('SG', '40'),
('KL', '50');

CREATE TABLE



INSERT 0 3


SELECT
	l.code,
	SUM(p.amount)
FROM
	locations l
LEFT JOIN payments p 
ON p.location_code = l.code
GROUP by l.code


code
sum


KL
100

SG
40

JKR
null



SELECT 3


SELECT
	l.code,
	l.location_name,
	SUM(p.amount)
FROM
	locations l
LEFT JOIN payments p 
ON p.location_code = l.code
GROUP by l.code

ERROR:  column "l.location_name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 3:  l.location_name,
         ^