Postgres 15

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 new_table (data text, created_date timestamp default now());
insert into new_table (data) values ('value1');
select * from new_table;

CREATE TABLE

INSERT 0 1

data
created_date

value1
2023-07-04 13:53:59.674347

SELECT 1

alter table new_table rename to new_table_raw;
    
create view new_table as 
select * from new_table_raw where created_date>now()-'3 months'::interval;
    
--insert, update, delete still work even though it's a view now
insert into new_table (data) values ('value2');
select * from new_table;

ALTER TABLE

CREATE VIEW

INSERT 0 1

data
created_date

value1
2023-07-04 13:53:59.674347

value2
2023-07-04 13:53:59.683232

SELECT 2

delete from new_table where data='value2';
select * from new_table;

DELETE 1

data
created_date

value1
2023-07-04 13:53:59.674347

SELECT 1

update new_table set data='value3' where data='value1';
select * from new_table;

UPDATE 1

data
created_date

value3
2023-07-04 13:53:59.674347

SELECT 1

data	created_date
value1	2023-07-04 13:53:59.674347
value2	2023-07-04 13:53:59.683232