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 mytable
("id" int, "name" varchar(5), "field1" varchar(4), "field2" varchar(3), "field3" varchar(3))
;
INSERT INTO mytable VALUES
(1, 'igig', 'a', 'b', 'cde'),
(2, 'ihiuh', 'gf', 'hi', 'pl'),
(3, 'pio', 'zsfs', 'oij', 'ugu'),
(4, 'xrxf', 'iuhi', 'vxz', 'pkm'),
(5, 'rwtre', 'xrsd', 'as', 'jhb');
SELECT * FROM mytable
CREATE TABLE
INSERT 0 5
| id | name | field1 | field2 | field3 |
|---|---|---|---|---|
| 1 | igig | a | b | cde |
| 2 | ihiuh | gf | hi | pl |
| 3 | pio | zsfs | oij | ugu |
| 4 | xrxf | iuhi | vxz | pkm |
| 5 | rwtre | xrsd | as | jhb |
SELECT 5
SELECT
id,
json_build_object('column_name', 'name', 'value', name) AS name,
json_build_object('column_name', 'field1', 'value', field1) AS field1,
json_build_object('column_name', 'field2', 'value', field2) AS field2,
json_build_object('column_name', 'field3', 'value', field3) AS field3
FROM
mytable
| id | name | field1 | field2 | field3 |
|---|---|---|---|---|
| 1 | {"column_name" : "name", "value" : "igig"} | {"column_name" : "field1", "value" : "a"} | {"column_name" : "field2", "value" : "b"} | {"column_name" : "field3", "value" : "cde"} |
| 2 | {"column_name" : "name", "value" : "ihiuh"} | {"column_name" : "field1", "value" : "gf"} | {"column_name" : "field2", "value" : "hi"} | {"column_name" : "field3", "value" : "pl"} |
| 3 | {"column_name" : "name", "value" : "pio"} | {"column_name" : "field1", "value" : "zsfs"} | {"column_name" : "field2", "value" : "oij"} | {"column_name" : "field3", "value" : "ugu"} |
| 4 | {"column_name" : "name", "value" : "xrxf"} | {"column_name" : "field1", "value" : "iuhi"} | {"column_name" : "field2", "value" : "vxz"} | {"column_name" : "field3", "value" : "pkm"} |
| 5 | {"column_name" : "name", "value" : "rwtre"} | {"column_name" : "field1", "value" : "xrsd"} | {"column_name" : "field2", "value" : "as"} | {"column_name" : "field3", "value" : "jhb"} |
SELECT 5
SELECT
id,
ARRAY[name, field1, field2, field3]
FROM (
SELECT
id,
json_build_object('column_name', 'name', 'value', name) AS name,
json_build_object('column_name', 'field1', 'value', field1) AS field1,
json_build_object('column_name', 'field2', 'value', field2) AS field2,
json_build_object('column_name', 'field3', 'value', field3) AS field3
FROM
mytable
)s
| id | array |
|---|---|
| 1 | {"{"column_name" : "name", "value" : "igig"}","{"column_name" : "field1", "value" : "a"}","{"column_name" : "field2", "value" : "b"}","{"column_name" : "field3", "value" : "cde"}"} |
| 2 | {"{"column_name" : "name", "value" : "ihiuh"}","{"column_name" : "field1", "value" : "gf"}","{"column_name" : "field2", "value" : "hi"}","{"column_name" : "field3", "value" : "pl"}"} |
| 3 | {"{"column_name" : "name", "value" : "pio"}","{"column_name" : "field1", "value" : "zsfs"}","{"column_name" : "field2", "value" : "oij"}","{"column_name" : "field3", "value" : "ugu"}"} |
| 4 | {"{"column_name" : "name", "value" : "xrxf"}","{"column_name" : "field1", "value" : "iuhi"}","{"column_name" : "field2", "value" : "vxz"}","{"column_name" : "field3", "value" : "pkm"}"} |
| 5 | {"{"column_name" : "name", "value" : "rwtre"}","{"column_name" : "field1", "value" : "xrsd"}","{"column_name" : "field2", "value" : "as"}","{"column_name" : "field3", "value" : "jhb"}"} |
SELECT 5
SELECT
id,
elements
FROM (
SELECT
id,
json_build_object('column_name', 'name', 'value', name) AS name,
json_build_object('column_name', 'field1', 'value', field1) AS field1,
json_build_object('column_name', 'field2', 'value', field2) AS field2,
json_build_object('column_name', 'field3', 'value', field3) AS field3
FROM
mytable
)s,
unnest(ARRAY[name, field1, field2, field3]) AS elements
| id | elements |
|---|---|
| 1 | {"column_name" : "name", "value" : "igig"} |
| 1 | {"column_name" : "field1", "value" : "a"} |
| 1 | {"column_name" : "field2", "value" : "b"} |
| 1 | {"column_name" : "field3", "value" : "cde"} |
| 2 | {"column_name" : "name", "value" : "ihiuh"} |
| 2 | {"column_name" : "field1", "value" : "gf"} |
| 2 | {"column_name" : "field2", "value" : "hi"} |
| 2 | {"column_name" : "field3", "value" : "pl"} |
| 3 | {"column_name" : "name", "value" : "pio"} |
| 3 | {"column_name" : "field1", "value" : "zsfs"} |
| 3 | {"column_name" : "field2", "value" : "oij"} |
| 3 | {"column_name" : "field3", "value" : "ugu"} |
| 4 | {"column_name" : "name", "value" : "xrxf"} |
| 4 | {"column_name" : "field1", "value" : "iuhi"} |
| 4 | {"column_name" : "field2", "value" : "vxz"} |
| 4 | {"column_name" : "field3", "value" : "pkm"} |
| 5 | {"column_name" : "name", "value" : "rwtre"} |
| 5 | {"column_name" : "field1", "value" : "xrsd"} |
| 5 | {"column_name" : "field2", "value" : "as"} |
| 5 | {"column_name" : "field3", "value" : "jhb"} |
SELECT 20
SELECT
id,
elements ->> 'column_name' AS column_name,
elements ->> 'value' AS value
FROM (
SELECT
id,
json_build_object('column_name', 'name', 'value', name) AS name,
json_build_object('column_name', 'field1', 'value', field1) AS field1,
json_build_object('column_name', 'field2', 'value', field2) AS field2,
json_build_object('column_name', 'field3', 'value', field3) AS field3
FROM
mytable
)s,
unnest(ARRAY[name, field1, field2, field3]) AS elements
| id | column_name | value |
|---|---|---|
| 1 | name | igig |
| 1 | field1 | a |
| 1 | field2 | b |
| 1 | field3 | cde |
| 2 | name | ihiuh |
| 2 | field1 | gf |
| 2 | field2 | hi |
| 2 | field3 | pl |
| 3 | name | pio |
| 3 | field1 | zsfs |
| 3 | field2 | oij |
| 3 | field3 | ugu |
| 4 | name | xrxf |
| 4 | field1 | iuhi |
| 4 | field2 | vxz |
| 4 | field3 | pkm |
| 5 | name | rwtre |
| 5 | field1 | xrsd |
| 5 | field2 | as |
| 5 | field3 | jhb |
SELECT 20