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