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 Author
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.
One more possible solution is dbForge Studio for PostgreSQL with its reach import\export tool that it supports file transfer between the most frequently used data formats (Text, MS Excel, XML, CSV, JSON, etc.) https://www.devart.com/dbforge/postgresql/studio/data-export-import.html
nice. I have another example for the same http://www.etechpulse.com/2013/06/database-how-to-import-data-from-csv-in.html
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.
it’s good but the i cant see the data in it,it is in encoded formate..!
After all I found out how to do it correctly, FYI:
http://www.postgresql.org/docs/8.1/static/sql-copy.html
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 | -170,562 6 | de | Andorra | 6 | 42,656 | 42,4364 | 1,42139 | 1,78172 [...]Thanks a lot.
you saved my time yeah.
I did not get this line: “yongmo@abcdb:~$ psql -p 5433 -U dba dbname” pls help. Thanks…
psqlis PostgreSQL command. This means, connect to postgreSQL database via port 5433.Awesome! thanks
Hi mkyong,
I think also need to \o after completion….
Oops looks like someone else already suggested copy.
I wish they had mentioned that it would handle the escaping cleanly.
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.
Thanks for your tips
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.
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.
Hi , thanks for your comment, i didn’t know about it. Ya it may not work in your case (escaping) , however it work fine in most of the case, usually data wont care about escape right?
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.
hi ressu,
Thanks for tips ~
Hi No,
Is /c:/no a folder? Just type “\o /c:/no/dummy.csv”
Hope help
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
Is your /W2k3fs3/shared/Transfer/ folder exists? Do you have permission to create file in that folder?
how to check permission to create file
1) Make sure /W2k3fs3/shared/Transfer/ directory exist
2) Try create a file at /W2k3fs3/shared/Transfer/ directory
if you cant create the file, means you do not have permission to do it, pls ask your Unix admin to assign proper permission for you.
Here is a Unix permission article, may be you can understanding more from this article
http://www.elated.com/articles/understanding-permissions/
Thank.
if i want to save in my computer in /c:/no file can you rewrite command for me please
Many Thank.
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!
thanks for tips, your comment is very helpful to me ~