Postgres 12

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 ( id INT GENERATED ALWAYS AS IDENTITY,
                      firstName VARCHAR(100),
                      lastName VARCHAR(100),
                      fullName VARCHAR(200) );
INSERT INTO people (firstName, lastName) VALUES
('firstName1', 'lastName1'),
('firstName2', 'lastName2');
SELECT * FROM people;
2 rows affected



id
firstname
lastname
fullname


1
firstName1
lastName1
null

2
firstName2
lastName2
null


ALTER TABLE people
    DROP COLUMN fullName,
    ADD COLUMN fullName VARCHAR(200) GENERATED ALWAYS AS (firstName || ' ' || lastName) STORED;
SELECT * FROM people;
INSERT INTO people (firstName, lastName) VALUES
('firstName3', 'lastName3');
SELECT * FROM people;
UPDATE people SET firstName = firstname || '_changed';
SELECT * FROM people;


id
firstname
lastname
fullname


1
firstName1
lastName1
firstName1 lastName1

2
firstName2
lastName2
firstName2 lastName2


1 rows affected



id
firstname
lastname
fullname


1
firstName1
lastName1
firstName1 lastName1

2
firstName2
lastName2
firstName2 lastName2

3
firstName3
lastName3
firstName3 lastName3


3 rows affected



id
firstname
lastname
fullname


1
firstName1_changed
lastName1
firstName1_changed lastName1

2
firstName2_changed
lastName2
firstName2_changed lastName2

3
firstName3_changed
lastName3
firstName3_changed lastName3

id	firstname	lastname	fullname
1	firstName1	lastName1	firstName1 lastName1
2	firstName2	lastName2	firstName2 lastName2

id	firstname	lastname	fullname
1	firstName1_changed	lastName1	firstName1_changed lastName1
2	firstName2_changed	lastName2	firstName2_changed lastName2
3	firstName3_changed	lastName3	firstName3_changed lastName3