Data in a table to a csv file

I have a small problem,I need to export data from a table
into a csv file.For this i am using pqxx library to work on it.I can
able to retrieve the data from the database but the only problem is
I am unable to store it in a csv file.Can you guys please help me
how to do that.

I'm no Postgresql expert, but I've used the builtin "COPY" SQL
command. You can find the documentation on it here: http://www.postgresql.org/docs/8.3/interactive/sql-copy.html

Note:
1. Using COPY in this way is that Postgresql will create the csv file
on the server's filesystem, not on your client machine, if you use a
different machine for server and client.
2. when you create the file, the directory that you create the file in
must be writable by Postgres, I just always use /tmp as anyone can
create files in that directory.
3. #2 is probably specific to UNIX environments like Linux or MacOS X.

HTH,

-Joshua

Joshua Berry

Joshua Berry wrote:

I have a small problem,I need to export data from a table
into a csv file.For this i am using pqxx library to work on it.I can
able to retrieve the data from the database but the only problem is I
am unable to store it in a csv file.Can you guys please help me how
to do that.

I'm no Postgresql expert, but I've used the builtin "COPY" SQL
command. You can find the documentation on it
here: http://www.postgresql.org/docs/8.3/interactive/sql-copy.html

Note:
1. Using COPY in this way is that Postgresql will create the csv file
on the server's filesystem, not on your client machine, if you use a
different machine for server and client.
2. when you create the file, the directory that you create the file in
must be writable by Postgres, I just always use /tmp as anyone can
create files in that directory.
3. #2 is probably specific to UNIX environments like Linux or MacOS X.

You can have the copy routed to stdout on the client side. In psql
(using an area-code/prefix table I have for example):
copy (select npa,nxx,state from npanxx limit 10) to stdout csv header;

Capture, (or reroute with \o in psql) the output as appropriate for your
app. In this example, relevant file permissions are those of the client,
not the server. Remove "header" if you want the data only or rename the
headers with "as..." if you need (...npa as "Area Code", nxx as
"Prefix"...).

Alternately use whatever csv support your client language supports
(Python's csv module for example).

Cheers,
Steve