Exporting PostgreSQL result set to CSV
I probably use PostgreSQL just as much as MySQL these days due to client requirements and legacy applications I need to support. I don’t mind this; I actually like PostgreSQL and used it extensively at university. One problem I do have with it though is the fact there is no real support for exporting data in CSV format. In MySQL you can do something like:
SELECT * INTO OUTFILE '/path/to/file/extract.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM `users`
WHERE first_name='John';
This works a treat and will generate a nicely formatted file for you which you can open in Excel.
I have found the best way to replicate this functionality in PostgreSQL is to firstly run the \a command in the console to get unaligned results. If you don’t, running the PostgreSQL equivalent query:
SELECT * FROM "users" WHERE first_name='John' \o /path/to/file/extract.csv
will generate this:
first_name | last_name
------------+-----------
John | Smith
John | Brown
I then either open the unaligned file in Komodo and run a regular expression to replace all the pipe characters with commas or open the file in Excel and set the delimiter to be the pipe character.