PostgreSQL How to export table data to file / csv

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