Skip site navigation (1) Skip section navigation (2)

Re: COPY without quoting

From: Lee Hachadoorian <lee(dot)hachadoorian(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "PostgreSQL (SQL)" <pgsql-sql(at)postgresql(dot)org>
Subject: Re: COPY without quoting
Date: 2012-03-15 16:52:45
Message-ID: 4F621E5D.2050206@gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
On 03/15/2012 12:23 PM, Tom Lane wrote:
> Lee Hachadoorian<lee(dot)hachadoorian(at)gmail(dot)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


In response to

pgsql-sql by date

Next:From: Rehan SaleemDate: 2012-03-19 19:40:06
Subject: Postgresql function which compares values from both tables
Previous:From: Tom LaneDate: 2012-03-15 16:23:34
Subject: Re: COPY without quoting

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group