Postgres 14

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

drop table if exists content;
create table content (
    id int,
    dynamic_fields jsonb
);
  
insert into content values 
(0, '{
    "key1": "aaaaa text1",
    "key2": "text1",
    "key3": "blabla"}'::jsonb),
(1, '{
    "key5": "aaaaa text1",
    "key6": "text1",
    "key7": "blabla"}'::jsonb);

DROP TABLE

CREATE TABLE

INSERT 0 2

update content
set dynamic_fields = 
    dynamic_fields || 
    jsonb_build_object('key1', replace(dynamic_fields->>'key1', 'text1', 'text2'))
where dynamic_fields ? 'key1'
returning *

id
dynamic_fields

0
{"key1": "aaaaa text2", "key2": "text1", "key3": "blabla"}

UPDATE 1

select *
from content
order by id

id
dynamic_fields

0
{"key1": "aaaaa text2", "key2": "text1", "key3": "blabla"}

1
{"key5": "aaaaa text1", "key6": "text1", "key7": "blabla"}

SELECT 2

id	dynamic_fields
0	{"key1": "aaaaa text2", "key2": "text1", "key3": "blabla"}
1	{"key5": "aaaaa text1", "key6": "text1", "key7": "blabla"}