Postgres 9.6

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 9.6.23 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit


create table blocks (id int, name varchar, blocks text);

insert into blocks values(
1,   'Test 1',  '[{"name": "Bob", "type": "INFO", "title": "CFO"}, {"type": "FOOTER"}]'),
(2,   'Another', '[{"name": "Bob", "type": "INFO", "title": "Manager"}, {"type": "FOOTER"}]'),
(3,   'Final',   '[{"id": 22, "type": "IMAGE"}]')
3 rows affected

select 
*  
from blocks cross join lateral jsonb_array_elements(blocks::jsonb) t(x)


id
name
blocks
x


1
Test 1
[{"name": "Bob", "type": "INFO", "title": "CFO"}, {"type": "FOOTER"}]
{"name": "Bob", "type": "INFO", "title": "CFO"}

1
Test 1
[{"name": "Bob", "type": "INFO", "title": "CFO"}, {"type": "FOOTER"}]
{"type": "FOOTER"}

2
Another
[{"name": "Bob", "type": "INFO", "title": "Manager"}, {"type": "FOOTER"}]
{"name": "Bob", "type": "INFO", "title": "Manager"}

2
Another
[{"name": "Bob", "type": "INFO", "title": "Manager"}, {"type": "FOOTER"}]
{"type": "FOOTER"}

3
Final
[{"id": 22, "type": "IMAGE"}]
{"id": 22, "type": "IMAGE"}


with cte as 
(
select 
id,name,bool_or(t.x->>'type'='INFO') "new_value"
from blocks cross join lateral jsonb_array_elements(blocks::jsonb) t(x)
group by 1,2
)

update blocks t1 
set name='New Name'
from cte t2 
where t1.id=t2.id and t2.new_value=true;
2 rows affected

select * from blocks


id
name
blocks


3
Final
[{"id": 22, "type": "IMAGE"}]

1
New Name
[{"name": "Bob", "type": "INFO", "title": "CFO"}, {"type": "FOOTER"}]

2
New Name
[{"name": "Bob", "type": "INFO", "title": "Manager"}, {"type": "FOOTER"}]

id	name	blocks	x
1	Test 1	[{"name": "Bob", "type": "INFO", "title": "CFO"}, {"type": "FOOTER"}]	{"name": "Bob", "type": "INFO", "title": "CFO"}
1	Test 1	[{"name": "Bob", "type": "INFO", "title": "CFO"}, {"type": "FOOTER"}]	{"type": "FOOTER"}
2	Another	[{"name": "Bob", "type": "INFO", "title": "Manager"}, {"type": "FOOTER"}]	{"name": "Bob", "type": "INFO", "title": "Manager"}
2	Another	[{"name": "Bob", "type": "INFO", "title": "Manager"}, {"type": "FOOTER"}]	{"type": "FOOTER"}
3	Final	[{"id": 22, "type": "IMAGE"}]	{"id": 22, "type": "IMAGE"}

id	name	blocks
3	Final	[{"id": 22, "type": "IMAGE"}]
1	New Name	[{"name": "Bob", "type": "INFO", "title": "CFO"}, {"type": "FOOTER"}]
2	New Name	[{"name": "Bob", "type": "INFO", "title": "Manager"}, {"type": "FOOTER"}]