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.)