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

select version();

version

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

SELECT 1

create table table_a( a_id integer generated always as identity 
                    , amounts  decimal
                    , currency_conversion_rate decimal
                    , amount_hkd decimal generated always as ( amounts * currency_conversion_rate ) stored
                    ); 
                    
insert into table_a(amounts,currency_conversion_rate) 
       values (100.00,1.5), (200.00,0.75);
 
select * 
  from table_a; 
 
CREATE TABLE

INSERT 0 2

a_id
amounts
currency_conversion_rate
amount_hkd

1
100.00
1.5
150.000

2
200.00
0.75
150.0000

SELECT 2

update table_a
   set amounts = 300
 where a_id = 1; 
 
 select * 
  from table_a;

UPDATE 1

a_id
amounts
currency_conversion_rate
amount_hkd

2
200.00
0.75
150.0000

1
300
1.5
450.0

SELECT 2

update  table_a 
   set amount_hkd = 100.50
 where a_id = 2; 

ERROR:  column "amount_hkd" can only be updated to DEFAULT
DETAIL:  Column "amount_hkd" is a generated column.