Last Updated: February 25, 2016
· tmilewski

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.


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

5 Responses
Add your response


Great tip. To make things even easier, rather than copying all the hostname, username, password values etc. from the heroku config, you can use the heroku pg:psql command to run psql with all the correct config and connect directly to your heroku PG.

over 1 year ago ·

Excellent :) Just used it. Thumbs up!

over 1 year ago ·
574e7e46e4593de1f2f4dc7e5b1cf03d normal

Awesome, exactly what I was looking for. Thank you.

over 1 year ago ·

Great :) (y) Thank You..!

over 1 year ago ·
2d3693d8cb3c65c1fab451330eb382ae normal

I only run "heroku pg:psql" and "\copy (SELECT firstname, lastname, email FROM users) TO dump.csv CSV DELIMITER ',' ".

over 1 year ago ·