Обсуждение: 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.

Thanks in advance.
Mary Wang

> -----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
todisk : 

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:

> 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
>
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

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
tableto 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/