Simple Export to CSV with Postgres

If you're like me you probably use Heroku and their "SQL Database as a Service." Heroku, somewhat recently, created a neat feature called "Data Clips" which allows a user to run a query and share the results with others. From there the data may be downloaded in multiple formats making life just a little bit easier.

There's just one issue, it doesn't handle large datasets well.

Here's a simple way to retrieve your data in a CSV format from Postgres.

1) Find your PSQL URL from the Heroku Posgres admin panel and run it in your terminal:

psql "dbname=foo host=bar.compute-1.amazonaws.com user=abc password=123 port=5432 sslmode=require"

2) Run the following command with your query inside the parenthesis:

\copy (SELECT first_name, last_name, email FROM users) TO dump.csv CSV DELIMITER ','

3) Go ahead and close the connection with "\q" and you'll find your new CSV in your current working directory.

Enjoy!

(Note: While this is written in the context of Heroku, it is in no way exclusive to their services.)