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

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
owner@postgresql.org] On Behalf Of Wang, Mary Y
Sent: Monday, October 04, 2010 1:00 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] How to data dump a table content to a CSV or XML
format?

Hi All,

I'd like to do a data dump of a table to a CSV or XML file.
How would I do that?

I'm running on Postgres 8.3.8.

Did you see this:
http://www.postgresql.org/docs/8.3/interactive/sql-copy.html

On 10/04/2010 12:59 PM, Wang, Mary Y wrote:

Hi All,

I'd like to do a data dump of a table to a CSV or XML file.
How would I do that?

I'm running on Postgres 8.3.8.

Thanks in advance.
Mary Wang

As sql COPY"
http://www.postgresql.org/docs/8.3/interactive/sql-copy.html
In psql \copy
http://www.postgresql.org/docs/8.3/interactive/app-psql.html

COPY is done from the standpoint of the server so the file location must
be accessible to the server.

\copy is done from the standpoint of the client.
--
Adrian Klaver
adrian.klaver@gmail.com

Or in psql you can look at the help (\?) and set the output format to unaligned, comma separated, and output the table to disk :

database=> \pset fieldsep ,
Field separator is ",".
database=> \a
Output format is unaligned.
database=> \o table.csv
database=> SELECT * FROM table;
database=> \o

cheers

Ben

On 05/10/2010, at 6:59 AM, Wang, Mary Y wrote:

On Oct 4, 4:35 pm, li...@remoteinformation.com.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/