Postgres 10

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 test 
(   supplierid int,
    content  jsonb,
    operation_type text
);

INSERT INTO test(supplierid,content,operation_type) VALUES
    (1,'{"supplier":"s1","key2" : "value2"}', 'DEL'),
    (2,'{"supplier":"s2","key3" : "value3"}', 'DEL'),
    (3,'{"supplier":"s3","key3" : "value3"}', 'NON-DEL')
   ;

CREATE TABLE



INSERT 0 3


update test set content = jsonb_set(content, '{supplier}',  
          supplierId  :: text :: jsonb) 
 where operation_type = 'DEL'

UPDATE 2


select * from test;


supplierid
content
operation_type


3
{"key3": "value3", "supplier": "s3"}
NON-DEL

1
{"key2": "value2", "supplier": 1}
DEL

2
{"key3": "value3", "supplier": 2}
DEL



SELECT 3

supplierid	content	operation_type
3	{"key3": "value3", "supplier": "s3"}	NON-DEL
1	{"key2": "value2", "supplier": 1}	DEL
2	{"key3": "value3", "supplier": 2}	DEL