save (postgre) sql output to csv file

Question

hi there,

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

thanks!

martin

Answer 1

I don't have pgadmin in front of me but working from memory it's just a matter of highlighting all or part of the result set you want, right clicking and selecting export. I'm pretty sure the export has a .csv option.

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 want something easy to re-use or automate, you can use Postgresql's built in COPY command. e.g.

Copy (Select * From foo) To '/tmp/test.csv' With CSV;

Answer 4

In pgAdmin III there is an option to export to file from the query window. In the main menu it's Query -> Execute to file or there's a button that does the same thing (it's a green triangle with a blue floppy disk as opposed to the plain green triangle which just runs the query). If you're not running the query from the query window then I'd do what IMSoP suggested and use the copy command.