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 IF NOT EXISTS climbers
(	street VARCHAR(75) NOT NULL,
	building VARCHAR(5) NOT NULL,
	apartment VARCHAR(5),
	full_address TEXT GENERATED ALWAYS AS 
    (street || ',' || case when apartment is not null then building || '-' || apartment else building end) STORED
);

CREATE TABLE

insert into climbers (street, building, apartment) values ('Wall street', 'A', '10');

INSERT 0 1

select * from climbers;

street
building
apartment
full_address

Wall street
A
10
Wall street,A-10

SELECT 1

insert into climbers (street, building, apartment) values ('5th Avenue', 'B', null);

INSERT 0 1

select * from climbers;

street
building
apartment
full_address

Wall street
A
10
Wall street,A-10

5th Avenue
B
null
5th Avenue,B

SELECT 2