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

-- target table
CREATE TEMP TABLE baas (
  id serial PRIMARY KEY
, foo_id int
, name text
, property int
);

-- source table with type json[] (???)
CREATE TABLE foos (
  id int
, baas json[]  -- type json[] - Postgres array of json (??)
);

INSERT INTO foos VALUES
  (1, '{"{\"name\": \"a1\", \"property\": \"1\"}"
       ,"{\"name\": \"b1\", \"property\": \"2\"}"}')    -- pg array with 2 json elements: JSON objects with two properites each
, (2, '{"[{\"name\": \"a2\", \"property\": \"1\"}
         ,{\"name\": \"b2\", \"property\": \"2\"}]"}')  -- pg array with 1 json element: JSON array with 2 objects each, two properites each
;

CREATE TABLE



CREATE TABLE



INSERT 0 2


-- unlikely case 1
INSERT INTO baas (foo_id, name, property)
SELECT f.id, b.name, b.property
FROM   foos f, unnest(f.baas) a(baas), json_populate_record(null::baas, a.baas) b
WHERE  f.id = 1
RETURNING *;

-- unlikely case 2
INSERT INTO baas (foo_id, name, property)
SELECT f.id, b.name, b.property
FROM   foos f, unnest(f.baas) a(baas), json_populate_recordset(null::baas, a.baas) b
WHERE  f.id = 2
RETURNING *;


id
foo_id
name
property


1
1
a1
1

2
1
b1
2



INSERT 0 2




id
foo_id
name
property


3
2
a2
1

4
2
b2
2



INSERT 0 2


-- source table with type json (!) - the likely case
DROP TABLE foos;
CREATE TEMP TABLE foos (
  id int
, baas json  -- type json (with nested JSON array)
);

INSERT INTO foos VALUES
  (3, '[{"name": "a", "property": "1"}
      , {"name": "b", "property": "2"}]')  -- 1 json array with 2 objects, each with 2 properties
;

DROP TABLE



CREATE TABLE



INSERT 0 1


-- likely case
INSERT INTO baas (foo_id, name, property)
SELECT f.id, b.name, b.property
FROM   foos f, json_populate_recordset(null::baas, f.baas) b
RETURNING *;


id
foo_id
name
property


5
3
a
1

6
3
b
2



INSERT 0 2


TABLE baas;


id
foo_id
name
property


1
1
a1
1

2
1
b1
2

3
2
a2
1

4
2
b2
2

5
3
a
1

6
3
b
2



SELECT 6