Postgres 11

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 myt (id int, name text, addr text);
INSERT INTO myt VALUES
	(1, 'n1', 'ad1'),
	(2, 'n2', 'ad2');

2 rows affected

-- Postgres 9.4+
SELECT id, json_build_object('name', name, 'addr', addr) AS data
FROM   myt;



id
data


1
{"name" : "n1", "addr" : "ad1"}

2
{"name" : "n2", "addr" : "ad2"}


-- Postgres 9.3 or older
-- OP's version (with missing alias fixed)
select * from (
  select id,
    (
      select row_to_json(d)
      from (
        select name, addr
        from myt d
        where d.id=s.id
      ) d
    ) as data
  from myt s
) x;



id
data


1
{"name":"n1","addr":"ad1"}

2
{"name":"n2","addr":"ad2"}


-- Simpler 1
SELECT id
     , (SELECT row_to_json(d)
        FROM  (
           SELECT name, addr
           FROM   myt
           WHERE  id = s.id
           ) d
       ) AS data
FROM   myt s;



id
data


1
{"name":"n1","addr":"ad1"}

2
{"name":"n2","addr":"ad2"}


-- Simpler 2
SELECT id
     , (SELECT row_to_json(d)
        FROM  (SELECT name, addr) d
       ) AS data
FROM   myt;



id
data


1
{"name":"n1","addr":"ad1"}

2
{"name":"n2","addr":"ad2"}


-- Simpler 3
SELECT id, row_to_json((SELECT d FROM (SELECT name, addr) d)) AS data
FROM   myt;


id
data


1
{"name":"n1","addr":"ad1"}

2
{"name":"n2","addr":"ad2"}

id	data
1	{"name" : "n1", "addr" : "ad1"}
2	{"name" : "n2", "addr" : "ad2"}