Postgres Pro
Downloads
Home   >   mailing lists

Thread: No quotes in output of "psql \copy CSV"

No quotes in output of "psql \copy CSV"

From
Arnaud Lesauvage
Date:
Hi all,

I'd like to generate CSV files from the output of a query.
The files must be generated on the client, so I was planning on using
psql \copy command.
I can't get the srings in the output to be quoted though. I thought that
this was the default for CSV, and even adding the "QUOTE" parameter does
not help :

postgres=# \copy (SELECT 1, 'test', 'foo', 'bar', TRUE, FALSE) TO stdout
CSV QUOTE '"' DELIMITER ','
1,test,foo,bar,t,f

postgres=# \copy (SELECT 1, 'test', 'foo', 'bar', TRUE, FALSE) TO stdout CSV
1,test,foo,bar,t,f


What am I missing here ?

Thanks for your help !
Regards
--
Arnaud Lesauvage

Re: No quotes in output of "psql \copy CSV"

From
Sam Mason
Date:
On Tue, Jun 29, 2010 at 02:24:00PM +0200, Arnaud Lesauvage wrote:
> I'd like to generate CSV files from the output of a query.
> I can't get the srings in the output to be quoted though. I thought that
> this was the default for CSV, and even adding the "QUOTE" parameter does
> not help :
>
> postgres=# \copy (SELECT 1, 'test', 'foo', 'bar', TRUE, FALSE) TO stdout
> CSV QUOTE '"' DELIMITER ','
> 1,test,foo,bar,t,f
>
> What am I missing here ?

By default values are only quoted when they need to be, i.e. if they
contain a quote, comma and a few other characters.  This is very common
behavior and all the programs I've tried to use the resulting files with
have been fine with it.

If you really want all values to be quoted you can include the "FORCE
QUOTE" option, i.e:

  copy (SELECT 1 AS a, 'a' AS b) TO stdout WITH CSV HEADER FORCE QUOTE a,b;

--
  Sam  http://samason.me.uk/

Re: No quotes in output of "psql \copy CSV"

From
Arnaud Lesauvage
Date:
Le 29/06/2010 14:40, Sam Mason a écrit :
> On Tue, Jun 29, 2010 at 02:24:00PM +0200, Arnaud Lesauvage wrote:
>>  I'd like to generate CSV files from the output of a query.
>>  I can't get the srings in the output to be quoted though. I thought that
>>  this was the default for CSV, and even adding the "QUOTE" parameter does
>>  not help :
>>
>>  postgres=# \copy (SELECT 1, 'test', 'foo', 'bar', TRUE, FALSE) TO stdout
>>  CSV QUOTE '"' DELIMITER ','
>>  1,test,foo,bar,t,f
>>
>>  What am I missing here ?
>
> By default values are only quoted when they need to be, i.e. if they
> contain a quote, comma and a few other characters.  This is very common
> behavior and all the programs I've tried to use the resulting files with
> have been fine with it.
>
> If you really want all values to be quoted you can include the "FORCE
> QUOTE" option, i.e:
>
>    copy (SELECT 1 AS a, 'a' AS b) TO stdout WITH CSV HEADER FORCE QUOTE a,b;


Oh indeed !
As a matter of fact, I don't even need to force the output to be quoted.
I just expected it to be so I thought there was an error.
But after trying with a string containing a comma, I do indeed so that
the string is quoted.

Thanks a lot Sam.

Regards
--
Arnaud Lesauvage