Re: How to data dump a table content to a CSV or XML format?

On Oct 4, 4:35 pm, li(dot)(dot)(dot)(at)remoteinformation(dot)com(dot)au (Ben Madin) wrote:
> Or in psql you can look at the help (\?) and set the output format to unaligned, comma separated, and output the table to disk :
[sNip]
> > I'd like to do a data dump of a table to a CSV or XML file.
> > How would I do that?
[sNip]
> database=> \pset fieldsep ,
> Field separator is ",".
> database=> \a
> Output format is unaligned.
> database=> \o table.csv
> database=> SELECT * FROM table;
> database=> \o
[sNip]

This is a good solution as long as none of the column data includes
comma characters.

Since commas are more likely to occur in CHAR/VARCHAR/TEXT/etc.
columns than tabs, I suggest using something like this instead
(assuming none of your columns contain tab {ASCII 9} characters):

database=> \pset fieldsep '\t'
Field separator is " ".
database=> \a
Output format is unaligned
database=> \o table.tab
database=> TABLE table;
database=> \o

--
The Lumber Cartel, local 42 (Canadian branch)
Beautiful British Columbia, Canada
http://www.lumbercartel.ca/