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