How to export table data to file / csv – PostgreSQL

PostgreSQL comes with an easy to use export tool to export data from PostgreSQL database. In this tutorial, we show you how to export data from PostgreSQL to a file or csv file.

1. Connect PostgreSQL

Use psql command to connect to the PostgreSQL database.


$ psql -p 5433 -U dba dbname

P.S 5433 is my PostgreSQL port number.

2. Export Ready

Type “\o /home/yongmo/data25000.csv“, it tell PostgreSQL that next query result will be exported to file “/home/yongmo/data25000.csv”.


dbname=> \o /home/yongmo/data25000.csv

3. Query to Export

Now, issue a normal query.


dbname=> select url from urltable where scoreid=1 limit 25000;

The entire query’s result will be exported to /home/yongmo/data25000.csv.

4. Full Example

Here is the full command.


yongmo@abcdb:~$ psql -p 5433 -U dba dbname
Password for user dba: 
Welcome to psql 8.2.4 (server 8.3.3), the PostgreSQL interactive terminal.
 
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit
 
WARNING:  You are connected to a server with major version 8.3,
but your psql client is major version 8.2.  Some backslash commands,
such as \d, might not work properly.
 
dbname=> \o /home/yongmo/data25000.csv
dbname=> select url from urltable where scoreid=1 limit 25000;
dbname=> \q

About the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments

avatar
19 Comment threads
10 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
15 Comment authors
Rkaustin healey 1962Atul makwanamo-cacherSaravanan Recent comment authors
newest oldest most voted
Rk
Guest
Rk
austin healey 1962
Guest
austin healey 1962

You made some really good points there. I checked on the net for more info about the issue
and found most individuals will go along with your views on
this web site.

Atul makwana
Guest
Atul makwana

it’s good but the i cant see the data in it,it is in encoded formate..!

mo-cacher
Guest
mo-cacher

After all I found out how to do it correctly, FYI:
http://www.postgresql.org/docs/8.1/static/sql-copy.html

mo-cacher
Guest
mo-cacher

This does not create an CSV file, result is like this: iddes | langid | label | id | north | south | west | east 1 | de | Armenien | 1 | 41,2971 | 38,8411 | 43,4542 | 46,6205 2 | de | Afghanistan | 2 | 38,472 | 29,4061 | 60,5042 | 74,9157 3 | de | Albanien | 3 | 42,6603 | 39,645 | 19,2885 | 21,0533 4 | de | Algerien | 4 | 37,0899 | 18,9764 | -8,66722 | 11,9865 5 | de | Amerikanisch Samoa | 5 | -14,2543 | -14,3756 | -170,823… Read more »

Saravanan
Guest
Saravanan

Thanks a lot.
you saved my time yeah.

trackback
query for exporting to csv postgres « Web Development

[…] to export table data to file / csv – PostgreSQL How to export table data to file / csv – PostgreSQL Comments (0) Trackbacks (0) Leave a comment […]

trackback
exporting tables « actionspeaksloudly
?eref AKYÜZ
Guest
?eref AKYÜZ

I did not get this line: “yongmo@abcdb:~$ psql -p 5433 -U dba dbname” pls help. Thanks…

droope
Guest
droope

Awesome! thanks

virender
Guest
virender

Hi mkyong,

I think also need to \o after completion….

Annoyed Programmed
Guest
Annoyed Programmed

Oops looks like someone else already suggested copy.

I wish they had mentioned that it would handle the escaping cleanly.

Annoyed Programmed
Guest
Annoyed Programmed

OK. I finally figured out the right way to do this.

Basically you need to use the COPY command. I.e.

COPY (SQL_QUERY) TO FILE_NAME WITH CSV HEADER

e.g.

psql -p 5433 -U dba dbname -c “COPY (select url from urltable where scoreid=1 limit 25000) TO STDOUT WITH CSV HEADER;” > /home/yongmo/data25000.csv

I’m glad this there’s a way to do this but I wish it was better documented.

Annoyed Programmed
Guest
Annoyed Programmed

PLEASE DON’T TRY THIS.

THIS DOESN’T DO ANY ESCAPING.

If your data contains comments in field data your files will break. Data with embedded new lines will be even worst.

Postgresql needs a real export to CSV for select statements. Unfortunately in does have this functionality.

kme
Guest
kme

You can handle the line breaks thusly, if url has line breaks in the results:

select ‘”‘||url||'”‘ from urltable where scoreid=1 limit 25000;

That will enclose the url results in quotation marks so the line breaks will be preserved without forcing them as newlines in the csv.

ressu
Guest
ressu

Why not use \copy

\copy (select url from urltable where scoreid=1 limit 25000) TO ‘/tmp/filename.csv’ CSV

\copy command can do various other tricks aswell.

No
Guest
No

I can not do it please advise me more i am very poorly

dbname=>\o /W2k3fs3/shared/Transfer/data25000.csv
/W2k3fs3/shared/Transfer/data25000.csv: No such file or directory

Kirk Bushell
Guest
Kirk Bushell

What’s even easier, is you can do this:

psql -p 5433 -U dba dbname -c “select url from urltable where scoreid=1 limit 25000;” > /home/yongmo/data25000.csv

Enjoy!

trackback
Solution Hacker - Common DBA jobs

[…] Export table data from postgresql to csv format […]