Postgres 16

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 regions (
  region varchar(20),
  kod_id int
);

insert into regions values
('Moscow region', 77),
('Belgorod Oblast', 31),
('Bryansk Oblast', 32),
('Vladimir Oblast', 33);

CREATE TABLE

INSERT 0 4

SELECT json_build_object('List of regions' , json_build_array ('Region name', region, 'region code', kod_id) ) 
FROM regions

json_build_object

{"List of regions" : ["Region name", "Moscow region", "region code", 77]}

{"List of regions" : ["Region name", "Belgorod Oblast", "region code", 31]}

{"List of regions" : ["Region name", "Bryansk Oblast", "region code", 32]}

{"List of regions" : ["Region name", "Vladimir Oblast", "region code", 33]}

SELECT 4

SELECT json_build_object('List of regions',
                         json_agg(
                             json_build_object ('Region name', region, 'region code', kod_id)
                         )
                        )
FROM regions

json_build_object

{"List of regions" : [{"Region name" : "Moscow region", "region code" : 77}, {"Region name" : "Belgorod Oblast", "region code" : 31}, {"Region name" : "Bryansk Oblast", "region code" : 32}, {"Region name" : "Vladimir Oblast", "region code" : 33}]}

SELECT 1