How to Export a PostgreSQL Table or Query to a CSV file

It’s often super useful to export a database table or an arbitrary SQL query to a local CSV file. Below is syntax to do just that using your psql client.

• Export an entire table named my_table to a local CSV file named my_file.csv:

  \COPY my_table TO './my_file.csv' WITH (FORMAT csv, DELIMITER ',', HEADER true);
  

• Export a subset of columns (col1 and col2) of a table named my_table to a local CSV file named my_file.csv:

  \COPY my_table(col1, col2) TO './my_file.csv' WITH (FORMAT csv, DELIMITER ',', HEADER true);
  

• Export the results of an arbitrary SQL query to a local CSV file name my_file.csv:

  \COPY (SELECT col1 FROM my_table) TO './my_file.csv' WITH (FORMAT csv, DELIMITER ',', HEADER true);