Postgres 15

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
    IF NOT EXISTS t1
(
    id                bigserial PRIMARY KEY,
    name              text,
    lat               varchar(255),
    long              varchar(255),
    rel_id varchar(255)
);

CREATE TABLE


insert into t1 (name, lat, long, rel_id) values ('apple', null, null, 1708237);
insert into t1 (name, lat, long, rel_id) values ('orange', null, '', 1703206);
insert into t1 (name, lat, long, rel_id) values ('cinnamon', '5', null, 1703217);
insert into t1 (name, lat, long, rel_id) values ('qiwi', '', '8', 1703209);
insert into t1 (name, lat, long, rel_id) values ('cake', '9', '10', 1703230);

INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1


select 
       coords->'rel_id' #>> '{}' as crel_id,
       (coords->'lat')::text as clat,
       (coords->'long')::text as clong
      from (select jsonb_array_elements('[
          {
            "rel_id": "1708237",
            "lat": 1,
            "long": 2
          },
          {
            "rel_id": "1703206",
            "lat": 3,
            "long": 4
          },
          {
            "rel_id": "1703217",
            "lat": 5,
            "long": 6
          },
          {
            "rel_id": "1703209",
            "lat": 7,
            "long": 8
          },
          {
            "rel_id": "1703230",
            "lat": 9,
            "long": 10
          }]'::jsonb) as coords) as data;


crel_id
clat
clong


1708237
1
2

1703206
3
4

1703217
5
6

1703209
7
8

1703230
9
10



SELECT 5


select * from t1;


id
name
lat
long
rel_id


1
apple
null
null
1708237

2
orange
null

1703206

3
cinnamon
5
null
1703217

4
qiwi

8
1703209

5
cake
9
10
1703230



SELECT 5


update t1
set lat  = json_data.clat,
    long = json_data.clong
from (select 
		 coords->'rel_id' #>> '{}' as crel_id, --convert jsonb value to string
		 coords->'lat' as clat,
		 coords->'long' as clong
         from (select jsonb_array_elements('[
          {
            "rel_id": "1708237",
            "lat": 1,
            "long": 2
          },
          {
            "rel_id": "1703206",
            "lat": 3,
            "long": 4
          },
          {
            "rel_id": "1703217",
            "lat": 5,
            "long": 6
          },
          {
            "rel_id": "1703209",
            "lat": 7,
            "long": 8
          },
          {
            "rel_id": "1703230",
            "lat": 9,
            "long": 10
          }]'::jsonb) as coords) 
              as coords_data)
        as json_data
where t1.rel_id = json_data.crel_id and
	-- check value may be null or empty for both 'lat' and 'long' columns
	(t1.long is null or t1.long = '' or t1.lat is null or t1.lat = '');

UPDATE 4


select * from t1
order by id;


id
name
lat
long
rel_id


1
apple
1
2
1708237

2
orange
3
4
1703206

3
cinnamon
5
6
1703217

4
qiwi
7
8
1703209

5
cake
9
10
1703230



SELECT 5

id	name	lat	long	rel_id
1	apple	null	null	1708237
2	orange	null		1703206
3	cinnamon	5	null	1703217
4	qiwi		8	1703209
5	cake	9	10	1703230