current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 214 down vote favorite
92

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.

share|improve this question

10 Answers 10

up vote 325 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;

This approach runs entirely on the remote server - it can't write to your local PC. It also needs to be run as a Postgres "superuser" (normally called "root") because Postgres can't stop it doing nasty things with that machine's local filesystem.

That doesn't actually mean you have to be connected as a superuser (automating that would be a security risk of a different kind), because you can use the SECURITY DEFINER option to CREATE FUNCTION to make a function which runs as though you were a superuser.

The crucial part is that your function is there to perform additional checks, not just by-pass the security - so you could write a function which exports the exact data you need, or you could write something which can accept various options as long as they meet a strict whitelist. You need to check two things:

  1. Which files should the user be allowed to read/write on disk? This might be a particular directory, for instance, and the filename might have to have a suitable prefix or extension.
  2. Which tables should the user be able to read/write in the database? This would normally be defined by GRANTs in the database, but the function is now running as a superuser, so tables which would normally be "out of bounds" will be fully accessible. You probably don’t want to let someone invoke your function and add rows on the end of your “users” table…

I've written a blog post expanding on this approach, including some examples of functions that export (or import) files and tables meeting strict conditions.

The other approach is to do the file handling on the client side, i.e. in your application or script. The Postgres server doesn't need to know what file you're copying to, it just spits out the data and the client puts it somewhere.

The underlying syntax for this is the COPY TO STDOUT command, and graphical tools like pgAdmin will wrap it for you in a nice dialog.

The psql command-line client has a special "meta-command" called \copy, which takes all the same options as the "real" COPY, but is run inside the client:

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

Note that there is no terminating ;, because meta-commands are terminated by newline, unlike SQL commands.

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.

Your application programming language may also have support for pushing or fetching the data, but you cannot generally use COPY FROM STDIN/TO STDOUT within a standard SQL statement, because there is no way of connecting the input/output stream. PHP's PostgreSQL handler (not PDO) includes very basic pg_copy_from and pg_copy_to functions which copy to/from a PHP array, which may not be efficient for large data sets.

share|improve this answer
1  
Well done that man! –  Dave Apr 4 '12 at 7:30
41  
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
4  
@Drachenfels: \copy works, too -- there, the paths are relative to the client, and no semicolon is needed/allowed. See my edit. –  krlmlr Feb 13 '13 at 10:12
    
@IMSoP: How would you add a COPY statement to an sql (on postgres 9.3) function? So the query gets saved to a .csv file? –  jO. Nov 12 '13 at 21:24
    
It looks like \copy needs to be a one-liner. So you don't get the beauty of formatting the sql the way you want, and just putting a copy/function around it. –  isaaclw Jan 17 at 13:49
up vote 117 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 '13 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 '13 at 9:09
    
Unfortunately, the first method doesn't include column headers, which makes it fairly useless for most applications... –  Cerin Mar 24 at 18:16
    
Also, psql doesn't quote cell values, so if ANY of your data uses the delimiter, your file will be corrupted. –  Cerin Apr 8 at 21:39
1  
@Cerin -t is a synonym for --tuples-only (turn off printing of column names and result row count footers, etc.) - omit it to get column headers –  ic3b3rg Jun 5 at 21:40
up vote 26 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 find your cvs file in this location

cd //tmp
copy it using scp command

or edit using nano:

nano //tmp/yourOutputFile.csv
share|improve this answer
2  
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
    
I'd improve this answer by noting that the "csv" output will not be properly escaped and each time a sql command is executed the results are concatenated to the output file. –  Danny Armstrong Feb 6 at 23:50
up vote 10 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
    
If the query is custom (I.E. having column aliases or joining different tables), the header will print out the column aliases just as it display on the screen. –  Devy Nov 13 '13 at 21:58
up vote 7 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 5 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 2 down vote

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

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

jackdb-export

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

share|improve this answer
    
This is awesome. Thanks! –  adback03 May 14 at 23:57
up vote 1 down vote

I'm working on AWS Redshift, which does not support the COPY TO feature.

My BI tool supports tab-delimited CSVs though, so I used the following:

 psql -h  dblocation  -p port -U user  -d dbname  -F $'\t' --no-align -c " SELECT *   FROM TABLE" > outfile.csv
share|improve this answer
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
up vote 0 down vote

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 works great!

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.