|
|
My boss ask me export some data from database for him to do statistic report. PostgreSQL provided easy and useful export feature to do it. Here i will demonstrate how to export data or query result from PostgreSQL into a file or csv file.
1) Connect to PostgreSQL with psql command
yongmo@abcdb:~$ psql -p 5433 -U dba dbname
P.S i installed my PostgreSQL in port 5433, so i need to specific port number.
P.S Type \? to view all available command
2) Type \o /home/yongmo/data25000.csv, it will export query result to /home/yongmo/data25000.csv file.
dbname=> \o /home/yongmo/data25000.csv
3) Type query that you want to export
dbname=> select url from urltable where scoreid=1 limit 25000;
4) Done, all query result exported to /home/yongmo/data25000.csv
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
|
|
|
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 ~
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.
Hi No,
Is /c:/no a folder? Just type “\o /c:/no/dummy.csv”
Hope help
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 ~
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.
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?
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
Oops looks like someone else already suggested copy.
I wish they had mentioned that it would handle the escaping cleanly.