I find myself needing to output CSV from database queries a lot, and like an Alzheimer’s patient I am constantly forgetting how to do it. Here’s the quick run-down on how you get queries from your database into a CSV file.

Postgres

\f ','
\a
\t
\o /tmp/output.csv
SELECT column_id, column_name FROM table_name;
\o
\q

The above sequence of slashes is how Postgres handles commands (\q to quit rather than MySQL’s ‘exit’).
\f – sets a comma to separate the results
\a – sets the output to be unaligned
\t – sets it to show only rows(tuples)
\o – sets the output path, and because most setups have you logging in as “postgres” without having a home directory, it is using the tmp directory
* – Your query, which will probably be slightly longer than the example.
\o – sets the output back to the screen
\q – quits!

MySQL

SELECT column_id, column_name INTO OUTFILE '/tmp/output.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY ‘\\’
LINES TERMINATED BY '\n'
FROM table_name;

Pretty cool how everything is crammed right into a single query, huh?