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.

See also http://stackoverflow.com/q/1120109/287948 – Peter Krauss Mar 29 '15 at 10:34 — Peter Krauss, Mar 29 '15 at 10:34

score 1449 · Accepted Answer · edited Apr 20 '20 at 19:57

Do you want the resulting file on the server, or on the client?

Server side

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 DELIMITER ',' HEADER;

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:

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

Client side

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.

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
@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 '14 at 13:49
It seems more the exception than the rule that you can run your command on the server. For this reason, I think sorin's answer is much better. — user411279, Mar 19 '14 at 17:57
@user411279 The big advantage of a properly secured use of `COPY FROM` on the server is that you can build it into a script or web application in your language of choice without having to rely on a separate shell command, which will need a DB password etc to connect. — IMSoP, Mar 19 '14 at 18:11
@user411279 Also, the `\copy` command in the second half of my answer is equivalent to the `> output.csv` in the first part of sorin's, and the `COPY` command in the second part of sorin's answer is the one I discuss in detail in the first half of mine. — IMSoP, Mar 19 '14 at 18:14
The COPY command is the easiest and the most efficient method to extract data from PostgreSQL in CSV format (it aslo provides the binary format which is slightly more efficient than CSV one). — ady, Jan 15 '16 at 22:04
I keep on forgetting the syntax of this command and keep on visiting your answer! — verisimilitude, Jul 29 '16 at 07:39
I have a complex SELECT in an external file. Any suggestion for the COPY syntax ? — Massimo, Oct 12 '16 at 14:28
I was not able to run `\copy` from pgAdmin? Do you know how to do it (it seems it was not recognizing the program)? I succeeded using it from the SQL Shell (psql) though. — Andre Silva, May 02 '18 at 17:29
@AndreSilva As the answer states, `\copy` is a special meta-command *in the `psql` command-line client*. It won't work in other clients, like pgAdmin; they will probably have their own tools, such as graphical wizards, for doing this job. — IMSoP, May 02 '18 at 17:49

score 563 · Answer 2 · edited Jun 20 '20 at 09:12

563

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>\f ','
psql>\a
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.

edited Jun 20 '20 at 09:12

Community

1
1

answered Aug 08 '12 at 17:56

sorin

137,198
150
472
707

34

IMHO the first option is error prone, because it doesn't include proper escaping of comma in exported data. – Piohen May 06 '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 07 '13 at 09:09
Unfortunately, the first method doesn't include column headers, which makes it fairly useless for most applications... – Cerin Mar 24 '14 at 18:16
4

Also, psql doesn't quote cell values, so if **ANY** of your data uses the delimiter, your file will be corrupted. – Cerin Apr 08 '14 at 21:39
7

@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 05 '14 at 21:40
22

Just tested the comma-escaping claim—it’s true, method #1 does *not* escape commas in values. – MrColes Sep 17 '14 at 21:07
I fixed #3 so it'll output as a CSV file. It still won't escape commas though. – MrValdez May 19 '15 at 06:51
Thanks for 3 solution. It doesn't need an superuser account. – Samuel Dauzon Sep 11 '15 at 07:39
Also, note that if you want to automate SSH interactions with the "psql" utility, there is no way to specify the password from the command prompt. Best way to deal with it is to create a password file as described here: http://www.postgresql.org/docs/9.4/static/libpq-pgpass.html – Bogdan Dec 18 '15 at 14:35
For method 1, I saved it as a tsv file and gave "}" as the field separator. Of course, if your data has `}`, this is a terrible idea. But it worked for me. I opened it in Excel, split them and it was fine. Not really advisable as a long term solution, but for one-offs it works fine. – CodingInCircles Jan 11 '17 at 19:29
For complex queries it is useful to just run a script file: psql -d dbname -t -A -F"," -f my-report.sql > my-report.csv – Dion Truter Jan 13 '17 at 12:00
1

also use "\pset footer" so the row counts don't hoe up in the file – techbrownbags May 08 '18 at 21:20
1

how can this include the column names? – Abel Callejo May 30 '18 at 08:43
@sorin please see this [comment](https://stackoverflow.com/questions/1517635/save-pl-pgsql-output-from-postgresql-to-a-csv-file/10979064#comment87502440_11870348). You can replace `-t` with `-P footer` (and add `\pset footer` interactively) to achieve the same goal, with the added value of retaining the header. I tried to edit your answer myself but it was rejected. – EliadL Sep 11 '19 at 10:50

score 98 · Answer 3 · edited Mar 16 '17 at 12:49

98

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

1) Set field seperator to ',':

\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 then be able to find your csv file in this location:

cd /tmp

Copy it using the scp command or edit using nano:

nano /tmp/yourOutputFile.csv

edited Mar 16 '17 at 12:49

yunque

475
1
7
16

answered Jun 11 '12 at 11:18

Marcin Wasiluk

4,285
2
31
42

4

and \o in order to print console again – metdos Aug 06 '12 at 14:57
2

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
5

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 06 '14 at 23:50
What about newlines in field values? The `COPY` or `\copy` approaches handle correctly (convert to standard CSV format); does this? – Wildcard Jan 07 '17 at 04:19
@MarcinWasiluk please see this [comment](https://stackoverflow.com/questions/1517635/save-pl-pgsql-output-from-postgresql-to-a-csv-file/10979064#comment87502440_11870348). You can replace `\t` with `\pset footer` to achieve the same goal, with the added value of retaining the header. I tried to edit your answer myself but it was rejected. – EliadL Sep 11 '19 at 10:48

joshperry · Answer 4 · 2018-10-30T20:29:37.553

CSV Export Unification

This information isn't really well represented. As this is the second time I've needed to derive this, I'll put this here to remind myself if nothing else.

Really the best way to do this (get CSV out of postgres) is to use the COPY ... TO STDOUT command. Though you don't want to do it the way shown in the answers here. The correct way to use the command is:

COPY (select id, name from groups) TO STDOUT WITH CSV HEADER

Remember just one command!

It's great for use over ssh:

$ ssh psqlserver.example.com 'psql -d mydb "COPY (select id, name from groups) TO STDOUT WITH CSV HEADER"' > groups.csv

It's great for use inside docker over ssh:

$ ssh pgserver.example.com 'docker exec -tu postgres postgres psql -d mydb -c "COPY groups TO STDOUT WITH CSV HEADER"' > groups.csv

It's even great on the local machine:

$ psql -d mydb -c 'COPY groups TO STDOUT WITH CSV HEADER' > groups.csv

Or inside docker on the local machine?:

docker exec -tu postgres postgres psql -d mydb -c 'COPY groups TO STDOUT WITH CSV HEADER' > groups.csv

Or on a kubernetes cluster, in docker, over HTTPS??:

kubectl exec -t postgres-2592991581-ws2td 'psql -d mydb -c "COPY groups TO STDOUT WITH CSV HEADER"' > groups.csv

So versatile, much commas!

Do you even?

Yes I did, here are my notes:

The COPYses

Using /copy effectively executes file operations on whatever system the psql command is running on, as the user who is executing it1. If you connect to a remote server, it's simple to copy data files on the system executing psql to/from the remote server.

COPY executes file operations on the server as the backend process user account (default postgres), file paths and permissions are checked and applied accordingly. If using TO STDOUT then file permissions checks are bypassed.

Both of these options require subsequent file movement if psql is not executing on the system where you want the resultant CSV to ultimately reside. This is the most likely case, in my experience, when you mostly work with remote servers.

It is more complex to configure something like a TCP/IP tunnel over ssh to a remote system for simple CSV output, but for other output formats (binary) it may be better to /copy over a tunneled connection, executing a local psql. In a similar vein, for large imports, moving the source file to the server and using COPY is probably the highest-performance option.

PSQL Parameters

With psql parameters you can format the output like CSV but there are downsides like having to remember to disable the pager and not getting headers:

$ psql -P pager=off -d mydb -t -A -F',' -c 'select * from groups;'
2,Technician,Test 2,,,t,,0,,                                                                                                                                                                   
3,Truck,1,2017-10-02,,t,,0,,                                                                                                                                                                   
4,Truck,2,2017-10-02,,t,,0,,

Other Tools

No, I just want to get CSV out of my server without compiling and/or installing a tool.

Where do the results get saved to ? My query runs but the file doesn't show up anywhere on my computer. This is what I'm doing : COPY (select a,b from c where d = '1') TO STDOUT WITH CSVHEADER > abcd.csv — kRazzy R, Apr 25 '18 at 17:00
@kRazzyR The output goes to stdout of the psql command, so ultimately whatever you do with stdout is where the data goes. In my examples I use '> file.csv' to redirect to a file. You want to make sure that is outside the command being sent to to the server through the psql -c parameter. See the 'local machine' example. — joshperry, Apr 26 '18 at 02:02
Thanks for the complete explanation. The copy command is hopelessly complex with psql. I end up usually using a free database client (dbeaver community edition) to import and export data files. It provides nice mapping and formatting tools. Your answer provides great detailed examples for copying from remote systems. — Rich Lysakowski PhD, Nov 28 '19 at 05:44

score 40 · Answer 5 · answered Jan 11 '13 at 20:34

40

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.

answered Jan 11 '13 at 20:34

benjwadams

1,208
10
16

1

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

maudulus · Answer 6 · 2015-05-26T13:19:12.243

28

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

edited May 26 '15 at 13:19

answered Jun 28 '14 at 21:41

maudulus

9,035
7
64
101

1

I had the permission denied error as well. Fixed it by sending to the `/tmp` folder first. For example: `\copy (SELECT * FROM messages) TO '/tmp/messages.csv' With CSV HEADER;` – Somto Muotoe Jan 01 '21 at 02:40

score 28 · Answer 7 · edited Jun 20 '20 at 09:12

New version - psql 12 - will support --csv.

psql - devel

--csv

Switches to CSV (Comma-Separated Values) output mode. This is equivalent to \pset format csv.

csv_fieldsep

Specifies the field separator to be used in CSV output format. If the separator character appears in a field's value, that field is output within double quotes, following standard CSV rules. The default is a comma.

Usage:

psql -c "SELECT * FROM pg_catalog.pg_tables" --csv  postgres

psql -c "SELECT * FROM pg_catalog.pg_tables" --csv -P csv_fieldsep='^'  postgres

psql -c "SELECT * FROM pg_catalog.pg_tables" --csv  postgres > output.csv

score 21 · Answer 8 · edited Mar 17 '19 at 22:32

21

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

edited Mar 17 '19 at 22:32

Mike T

34,456
15
128
169

answered Mar 27 '14 at 00:16

calcsam

271
3
5

Great, thanks! I've used ` psql -h dblocation -p port -U user -d dbname -F $',' --no-align -c "SELECT * FROM TABLE" > outfile.csv` to get CSVs. There's no quoting the fields, but it serves well enough for my purposes – Lightheaded Jun 16 '20 at 18:24

score 20 · Answer 9 · answered Oct 04 '09 at 23:12

20

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.

answered Oct 04 '09 at 23:12

Dirk Eddelbuettel

331,520
51
596
675

12

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 06 '09 at 05:19

score 12 · Answer 10 · answered Nov 04 '09 at 16:58

12

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.

answered Nov 04 '09 at 16:58

Amanda Nyren

485
2
6

IMSoP's answer didn't work for me as I needed to be a super admin. This worked a treat. Thanks! – Mike Neumegen Jan 31 '12 at 22:08

score 10 · Answer 11 · edited Nov 12 '18 at 00:40

10

I tried several things but few of them were able to give me the desired CSV with header details.

Here is what worked for me.

psql -d dbame -U username \
  -c "COPY ( SELECT * FROM TABLE ) TO STDOUT WITH CSV HEADER " > \
  OUTPUT_CSV_FILE.csv

edited Nov 12 '18 at 00:40

Synesso

34,066
32
124
194

answered Apr 02 '18 at 08:14

pyAddict

1,146
10
13

score 9 · Answer 12 · edited Mar 17 '19 at 22:30

9

I've written a little tool called psql2csv that encapsulates the COPY query TO STDOUT pattern, resulting in proper CSV. It's interface is similar to psql.

psql2csv [OPTIONS] < QUERY
psql2csv [OPTIONS] QUERY

The query is assumed to be the contents of STDIN, if present, or the last argument. All other arguments are forwarded to psql except for these:

-h, --help           show help, then exit
--encoding=ENCODING  use a different encoding than UTF8 (Excel likes LATIN1)
--no-header          do not output a header

edited Mar 17 '19 at 22:30

Mike T

34,456
15
128
169

answered Sep 18 '15 at 16:42

fphilipe

8,542
1
29
47

2

Works great. Thank you. – AlexM Nov 03 '17 at 06:52

score 7 · Answer 13 · answered Sep 18 '14 at 19:52

7

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

answered Sep 18 '14 at 19:52

Andres Kull

3,748
2
13
13

1

FWIW, I had to use `-F","` instead of `-F";"` to generate a CSV file that would open correctly in MS Excel – CFL_Jeff May 31 '18 at 19:44

score 5 · Answer 14 · answered Nov 30 '18 at 08:25

5

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

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

answered Nov 30 '18 at 08:25

Murli

430
5
10

score -2 · Answer 15 · edited Sep 15 '19 at 03:19

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
_{(source: jackdb.com)}

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

score -2 · Answer 16 · answered Dec 06 '19 at 04:21

Per the request of @skeller88, I am reposting my comment as an answer so that it doesn't get lost by people who don't read every response...

The problem with DataGrip is that it puts a grip on your wallet. It is not free. Try the community edition of DBeaver at dbeaver.io. It is a FOSS multi-platform database tool for SQL programmers, DBAs and analysts that supports all popular databases: MySQL, PostgreSQL, SQLite, Oracle, DB2, SQL Server, Sybase, MS Access, Teradata, Firebird, Hive, Presto, etc.

DBeaver Community Edition makes it trivial to connect to a database, issue queries to retrieve data, and then download the result set to save it to CSV, JSON, SQL, or other common data formats. It's a viable FOSS competitor to TOAD for Postgres, TOAD for SQL Server, or Toad for Oracle.

I have no affiliation with DBeaver. I love the price and functionality, but I wish they would open up the DBeaver/Eclipse application more and made it easy to add analytics widgets to DBeaver / Eclipse, rather than requiring users to pay for the annual subscription to create graphs and charts directly within the application. My Java coding skills are rusty and I don't feel like taking weeks to relearn how to build Eclipse widgets, only to find that DBeaver has disabled the ability to add third-party widgets to the DBeaver Community Edition.

Do DBeaver users have insight as to the steps to create analytics widgets to add into the Community Edition of DBeaver?

score -3 · Answer 17 · answered Feb 27 '18 at 10:56

-3

import json
cursor = conn.cursor()
qry = """ SELECT details FROM test_csvfile """ 
cursor.execute(qry)
rows = cursor.fetchall()

value = json.dumps(rows)

with open("/home/asha/Desktop/Income_output.json","w+") as f:
    f.write(value)
print 'Saved to File Successfully'

answered Feb 27 '18 at 10:56

user9279273

45
1
9

3

Please expolain what you did editing answer, avoid code only answer – GGO Feb 27 '18 at 12:09
3

Thank you for this code snippet, which might provide some limited short-term help. A proper explanation [would greatly improve](//meta.stackexchange.com/q/114762) its long-term value by showing *why* this is a good solution to the problem, and would make it more useful to future readers with other, similar questions. Please [edit] your answer to add some explanation, including the assumptions you've made. – Toby Speight Feb 27 '18 at 12:48
2

This will produce a json file, not a csv file. – nvoigt Feb 27 '18 at 13:23

Save PL/pgSQL output from PostgreSQL to a CSV file

17 Answers17

Server side

Client side

1 `psql` command

2 postgres `copy` command

3 psql interactive (or not)

4 pgadmin but that's not scriptable.

CSV Export Unification

Remember just one command!

Do you even?

The COPYses

PSQL Parameters

Other Tools

Linked

Related

Save PL/pgSQL output from PostgreSQL to a CSV file

17 Answers17

Server side

Client side

1 psql command

2 postgres copy command

3 psql interactive (or not)

4 pgadmin but that's not scriptable.

CSV Export Unification

Remember just one command!

Do you even?

The COPYses

PSQL Parameters

Other Tools

Linked

Related

1 `psql` command

2 postgres `copy` command