No quotes in output of "psql \copy CSV"
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
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,fWhat 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/
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,fWhat 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