jwtxjg
Last Updated: February 25, 2016
·
18.57K
· tmilewski
57862e3a0221453454ede7c096e232f8

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.)

5 Responses
Add your response

7174
B95de317ddc21d724cf14c6096bb9424

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
E813c2cd4046db5a1a5c3ef8a9077d0f

Excellent :) Just used it. Thumbs up!

over 1 year ago ·
10566
574e7e46e4593de1f2f4dc7e5b1cf03d normal

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

over 1 year ago ·
12681
9a6b6651d9a4bbc355e8625919ddcbc8

Great :) (y) Thank You..!

over 1 year ago ·
17701
2d3693d8cb3c65c1fab451330eb382ae normal

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

over 1 year ago ·