Thread: exporting quoted CSV data from a query

Is there a way to export the output of an arbitrary SQL query to a
quoted CSV file?

It seems that the COPY command can do that for regular tables (COPY foo
TO STDOUT WITH CSV QUOTE AS '"') but not for a query result. I could
create temp tables in a script but it seems that there should be a
simpler alternative.

The command-line psql can obviously be used with an arbitrary query, but
seems to fail on the quoting part--am I missing some option to psql that
would put quotes around fields that contain the quote character,
newlines, etc.?

What I am looking for is essentially a command-line alternative to the
pgAdmin export's "quoting" options.

George

On Mon, May 08, 2006 at 12:26:48PM -0700, George Pavlov wrote:
> Is there a way to export the output of an arbitrary SQL query to a
> quoted CSV file?

For now, you can do it with a temp table, as in:

BEGIN;
CREATE TEMP TABLE my_temp AS SELECT ...;
COPY my_temp ... ;
ROLLBACK;

HTH :)

Cheers,
D
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666
                              Skype: davidfetter

Remember to vote!