Replace multiple columns with single JSON column

I am running PostgreSQL 9.3.4. I have a table with 3 columns:

id
name
addr

1
n1
ad1

2
n2
ad2

I need to move the data to a new table with a JSON column like:

id
data

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

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

I tried:

SELECT t.id, row_to_json(t) AS data
FROM (SELECT id, name, addr FROM myt) t;

But that includes id in the result. So row_to_json is not the solution for me.

Is there a way to get only the columns I need (name & addr)?

Simplest with the operator jsonb - text → jsonb to remove a single key in Postgres 9.5 or later - after converting the whole row with to_jsonb(). (Cast the result to json if you don't want jsonb.)

SELECT id, to_jsonb(t.*) - 'id' AS data
FROM   myt t;

In Postgres 10 or later, you can also remove a whole array of keys with the operator jsonb - text[] → jsonb.

There is also a better option with json_build_object() in Postgres 9.4 or later:

SELECT id, json_build_object('name', name, 'addr', addr) AS data
FROM   myt;

But there is an even simpler way since Postgres 9.3:

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

to_json() is mostly the same as row_to_json().

Find a couple more syntax variants in the fiddle.

db<>fiddle here

Old sqlfiddle (Postgres 9.6)

Related answers:

-
Select columns inside json_agg

-
Return as array of JSON objects in SQL (Postgres)

-
Return total number of rows and selected (aggregated) data

StackExchange Database Administrators Q#90858, answer score: 100