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