Select Page

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

Leave a reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.