Re: [SQL] COPY without quoting

2012-03-15 Thread Tom Lane 
Lee Hachadoorian lee.hachadoor...@gmail.com writes:
 COPY ... TO ... WITH CSV defaults to quoting string fields with embedded 
 delimiters, quotes, and newlines. In pgAdmin I can execute to file and 
 specify no quoting for the output, in which case I get (what I want) a 
 file with no quotes, even though there are embedded commas in the 
 strings.

Uh ... why exactly would you want that?  It seems impossible to parse
such a file.

If what you want is an unparsable file, you could just strip out the
quotes with sed after the fact.  But COPY is not in the business of
producing non-machine-readable files, so the fact that it doesn't have
an option for this doesn't bother me.

regards, tom lane

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] COPY without quoting

2012-03-15 Thread Lee Hachadoorian 

On 03/15/2012 12:23 PM, Tom Lane wrote:

Lee Hachadoorianlee.hachadoor...@gmail.com  writes:

COPY ... TO ... WITH CSV defaults to quoting string fields with embedded
delimiters, quotes, and newlines. In pgAdmin I can execute to file and
specify no quoting for the output, in which case I get (what I want) a
file with no quotes, even though there are embedded commas in the
strings.

Uh ... why exactly would you want that?  It seems impossible to parse
such a file.

Admittedly, it's a hack, but I'm using array_to_string(..., ',') to 
concatenate several array columns which contain fairly long arrays. 
Strip out the quotes, and you do have a parsable CSV, with each array 
element becoming a column.


Your next question is probably why would I want to do *that*. Based on 
previous responses from this list, I use array columns to store the 
32,000 column US Census American Community Survey data set.


Stripping out quotes after the fact will be good enough. I'm not 
surprised that you find this option to be of limited use--it *is* of 
limited use, it just happens to apply to my use case--but I am surprised 
that it is possible in pgAdmin, since I thought pgAdmin just provided a 
GUI wrapper to standard Postgres commands. Probably a question for the 
pgAdmin list as to what's happening under the hood of Execute to file.


Best,
--Lee

--
Lee Hachadoorian
PhD, Earth  Environmental Sciences (Geography)
Research Associate, CUNY Center for Urban Research
http://freecity.commons.gc.cuny.edu


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql