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

select version();

version

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

SELECT 1

create table primary_tbl (a   integer  generated always as identity
                                       primary key
                         ,name text not null
                          ); 
           
insert into primary_tbl (name) values ('George'),('Mary'); 

select * 
  from primary_tbl;

CREATE TABLE

INSERT 0 2

a
name

1
George

2
Mary

SELECT 2

create table derived (a  integer
                     ,b  integer 
                     ,c  integer
                     ,constraint derived_uk
                                 unique (a,b,c)
                     ); 

insert into derived (a, b, c) 
     select a, ascii(substring(name,1,1)),3
       from primary_tbl; 

select *
  from derived;

CREATE TABLE

INSERT 0 2

a
b
c

1
71
3

2
77
3

SELECT 2

insert into derived (a, b, c)
  values (1,ascii('G'),3)
  on conflict (a, b, c) 
  do nothing;
  
select * 
  from derived;

INSERT 0 0

a
b
c

1
71
3

2
77
3

SELECT 2

alter table derived add column d text; 

ALTER TABLE

insert into derived (a, b, c, d)
  values (1,ascii('G'),3, 'some added text')
  on conflict (a, b, c) 
  do update 
        set d = excluded.d;

INSERT 0 1

select * 
  from derived;

a
b
c
d

2
77
3
null

1
71
3
some added text

SELECT 2