Darren O'Neill

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 for example. In PostgreSQL I am currently doing:

SELECT * FROM "users" WHERE first_name='John' \o /path/to/file/extract.csv

This generates the file:

 first_name | last_name
------------+-----------
 John       | Smith
 John       | Brown


Lots of spaces and pipes for all! I then open this file in Komodo and “convert” it using regular expressions. For example the regex below would be used:

All instances of "\s+\|\s+" would be converted to ","

I don’t recommend doing this because if you have 1000s of records the processing time is immense even on my iMac with 4Gb RAM.

One solution I have found suggests using the “\a” command, prior to running your query. This stops the output from aligning thus removing the problem of all the unnecessary white space characters.

I notice Jumping Bean is offering a solution as well which I will try out next week.

Tags: CSV, MySQL, PostgreSQL, RegEx, SQL