Hey guys, just a quick tip on something I used recently – how to dump data to CSV from postgres.
So, if you want to dump something on the server itself, you can run the following commands:
Copy (Select * From foo) To '/tmp/test.csv' With CSV DELIMITER ',';
Which will dump out the data into a CSV at the specified path. If you are logging in through a remote connection on a client, then you can do the same using a similar command:
\copy (Select * From foo) To '/tmp/test.csv' With CSV
This will then dump out locally to your client.
On both of these commands, the file will be written with the permissions of the item in question – with the first one, it will be written as the user which the server is running under (usually postgres) – in the second one it will be as whoever you ran psql as.
This was pulled from a stackoverflow answer, so go upvote them, its a very good answer and is very well written – much better than I have managed here.