PostgreSQL

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

Written by Ian Carnaghan · 29 sec read >
  • 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
Written by Ian Carnaghan
 I am a software developer and online educator who likes to keep up with all the latest in technology. I also manage cloud infrastructure, continuous monitoring, DevOps processes, security, and continuous integration and deployment. In my spare time I teach undergraduate classes in web development. Profile

Leave a Reply

avatar
  Subscribe  
Notify of
Up Next: How to execute SQL commands from a file in PostgreSQL