Monday, November 9, 2015

Dump a PostgreSQL table to json

This seems like it should be easy but it isn't, and I've had to look it up a couple times. Here's the magic juice:

copy (select array_to_json(array_agg(row_to_json(t)))
    from (
      select * from tweet_pgh limit 5
    ) t
) to '/home/dantasse/data_dump/foo.json';

gets 5 rows from the tweet_pgh table and dumps them to that file. (the directory has to be world-writeable I think, because it'll execute as the postgres user.)

COPY (SELECT ROW_TO_JSON(t) FROM (SELECT ST_AsGeoJSON(coordinates), * FROM tweet_austin) t) TO '/data/austin.json';
or just
COPY (SELECT ROW_TO_JSON(t) FROM (SELECT * FROM tweet_austin) t) TO '/data/austin.json';
seems to work better when you're memory limited.