jwtxjg
Last Updated: February 16, 2018
·
24.58K
· 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.

Enjoy!

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

6 Responses
Add your response

7174

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

Excellent :) Just used it. Thumbs up!

over 1 year ago ·
10566

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

over 1 year ago ·
12681

Great :) (y) Thank You..!

over 1 year ago ·
17701

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

over 1 year ago ·
29658

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

8 months ago ·