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.
\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!
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?