Last Updated: September 09, 2019
· 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 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.)

6 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 ·

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

over 1 year ago ·

Great :) (y) Thank You..!

over 1 year ago ·

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

over 1 year ago ·

This worked perfectly, but the column names don't get exported; is there any way to ensure the export contains these?

over 1 year ago ·