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.