How to send the output of a PostgreSQL query to a CSV file using the CLI (Command Line Interface)

Log into the PostgreSQL interface on the command line, you should see a similar screen like the one below

Welcome to psql 8.1.21, 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
 
database=# 

Type the following command
```
\o /tmp/output.csv
```
- Make sure the Postgres user has write privileges to the directory specified
- /tmp/output.csv is the directory and the csv file name
- \o = Put all query output into file output.csv
Run your sql query eg.
```
SELECT * FROM users;
```
Type \q to quit the PostgresSQL interface
Browse to the /tmp/ directory
Open the output.csv file and all the data from the users table will be within this CSV file, and the columns will be comma seperated.

Last Updated On April 07, 2018

How to send the output of a PostgreSQL query to a CSV file using the CLI (Command Line Interface)

Written by Ian Carnaghan

Leave a Reply