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

select version();

version

PostgreSQL 16.0 (Debian 16.0-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit

SELECT 1

CREATE TABLE person (id serial primary key, first_name text, last_name text, dob date);

CREATE TABLE

SELECT first_name, MAX(dob), ROW_NUMBER() OVER (PARTITION BY last_name ORDER BY first_name) FROM person GROUP BY 1;

ERROR:  column "person.last_name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...t_name, MAX(dob), ROW_NUMBER() OVER (PARTITION BY last_name ...
                                                             ^

SELECT first_name, MAX(dob), ROW_NUMBER() OVER (PARTITION BY last_name ORDER BY first_name) FROM person GROUP BY 1, last_name;

first_name
max
row_number

SELECT 0