Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 75 down vote favorite
29

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

share|improve this question

7 Answers

up vote 111 down vote accepted

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;

Note that this approach runs entirely on the remote server (it can't write to your local PC!), and requires you to be connected to Postgres as a "superuser" in order to write files to that machine's local filesystem.

If you are connecting using the psql command-line client (as opposed to a graphical tool such as pgAdmin), you can also use similar syntax to create a file local to the client:

\copy (Select * From foo) To '/tmp/test.csv' With CSV

From the docs:

Do not confuse COPY with the psql instruction \copy. \copy invokes COPY FROM STDIN or COPY TO STDOUT, and then fetches/stores the data in a file accessible to the psql client. Thus, file accessibility and access rights depend on the client rather than the server when \copy is used.

share|improve this answer
1  
Well done that man! – Dave Apr 4 '12 at 7:30
15  
Obviously above example requires sometimes user to be a superuser, here's a version for ordinary people ;) echo “COPY (SELECT * from foo) TO STDOUT with CSV HEADER” | psql -o '/tmp/test.csv' database_name – Drachenfels Apr 17 '12 at 17:26
3  
@Drachenfels: \copy works, too -- there, the paths are relative to the client, and no semicolon is needed/allowed. See my edit. – krlmlr Feb 13 at 10:12
up vote 39 down vote

There are several solutions:

1 psql command

psql -d dbname -t -A -F"," -c "select * from users" > output.csv

This has the big advantage that you can using it via SSH, like ssh postgres@host command - enabling you to get

2 postgres copy command

COPY (SELECT * from users) To '/tmp/output.csv' With CSV;

3 psql interactive (or not)

>psql dbname
psql>\o '/tmp/output.csv'
psql>SELECT * from users;
psql>\q

All of them can be used in scripts, but I prefer #1.

4 pgadmin but that's not scriptable.

share|improve this answer
IMHO the first option is error prone, because it doesn't include proper escaping of comma in exported data. – Piohen May 6 at 21:07
@Piohen as far as I remember it does because it will quote strings, but I'm not 100% sure, better to test. – sorin May 7 at 9:09
up vote 10 down vote

In terminal (while connected to the db) set output to the cvs file

1) set field seperator to ',' by:

\f ','

2) set output format unaligned:

\a

3) show only tuples

\t

4) set output

\o '/tmp/yourOutputFile.csv'

5) execute your query

select * from YOUR_TABLE

6) output

\o

you will be able to fin your cvs file in the 

cd //tmp
copy it using scp command

or edit using nano:

nano //tmp/yourOutputFile.csv
share|improve this answer
and \o in order to print console again – metdos Aug 6 '12 at 14:57
This will not produce a CSV file, it will just record the command output to the text file (which does not make it the comma-separated). – Ruslan Kabalin Nov 29 '12 at 16:39
@RuslanKabalin yes I have just notticed that and ammended instruction to create comma-separated output (cvs) – Marcin Wasiluk Nov 30 '12 at 11:01
up vote 4 down vote

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.

share|improve this answer
2  
This isn't a true CSV file--watch it burn if there are commas in the data--so using the built-in COPY support is preferred. But this general technique is handy as a quick hack for exporting from Postgres in other delimited formats besides CSV. – Greg Smith Oct 6 '09 at 5:19
up vote 2 down vote

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.

share|improve this answer
up vote 1 down vote

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.

share|improve this answer
IMSoP's answer didn't work for me as I needed to be a super admin. This worked a treat. Thanks! – Mike Jan 31 '12 at 22:08
up vote 0 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.