Postgres 14

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');

CREATE TABLE

INSERT 0 2

-- Postgres 9.5+ (optionally cast to json)
SELECT id, to_jsonb(t.*) - 'id' AS data
FROM   myt t;

id
data

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

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

SELECT 2

-- 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"}

SELECT 2

SELECT id
    , (SELECT 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"}

SELECT 2

SELECT id
    , (SELECT to_json(d.*) FROM (SELECT name, addr) d) AS data
FROM   myt;

id
data

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

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

SELECT 2

SELECT id
     , 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"}

SELECT 2

-- OP's version (with missing alias fixed) - Postgres 9.3 or older
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"}

SELECT 2

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

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