Save PL/pgSQL output from PostgreSQL to a CSV file

Question

What is the easiest way to save PL/pgSQL output from a PostgreSQL database to a CSV file?

I'm using PostgreSQL 8.4 with pgAdmin III and PSQL plugin where I run queries from.

Answer 1

I had to use the \COPY because I received the error message:

ERROR:  could not open file "/filepath/places.csv" for writing: Permission denied

So I used:

\Copy (Select address, zip  From manjadata) To '/filepath/places.csv' With CSV;

and it is functioning

Answer 2

psql can do this for you:

edd@ron:~$ psql -d beancounter -t -A -F"," \
                -c "select date, symbol, day_close " \
                   "from stockprices where symbol like 'I%' " \
                   "and date >= '2009-10-02'"
2009-10-02,IBM,119.02
2009-10-02,IEF,92.77
2009-10-02,IEV,37.05
2009-10-02,IJH,66.18
2009-10-02,IJR,50.33
2009-10-02,ILF,42.24
2009-10-02,INTC,18.97
2009-10-02,IP,21.39
edd@ron:~$

See man psql for help on the options used here.

Answer 3

If you have longer query and you like to use psql then put your query to a file and use the following command:

psql -d my_db_name -t -A -F";" -f input-file.sql -o output-file.csv

Answer 4

To Download CSV file with column names as HEADER use this command:

Copy (Select * From tableName) To '/tmp/fileName.csv' With CSV HEADER;

Answer 5

If you're interested in all the columns of a particular table along with headers, you can use

COPY table TO '/some_destdir/mycsv.csv' WITH CSV HEADER;

This is a tiny bit simpler than

COPY (SELECT * FROM table) TO '/some_destdir/mycsv.csv' WITH CSV HEADER;

which, to the best of my knowledge, are equivalent.

Answer 6

JackDB, a database client in your web browser, makes this really easy. Especially if you're on Heroku.

It lets you connect to remote databases and run SQL queries on them.

                                                                                                                                                       ^Source jackdb-heroku http://static.jackdb.com/assets/img/blog/jackdb-heroku-oauth-connect.gif

---

Once your DB is connected, you can run a query and export to CSV or TXT (see bottom right).

---

^{Note: I'm in no way affiliated with JackDB. I currently use their free services and think it's a great product.}