Thread: COPY without quoting
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. But I am missing it if the COPY statement has a way to turn quotes off. You can turn them *on* with FORCE QUOTE, in which case a column will be quoted even if there are no embedded delimiters, etc. I tried COPY ... TO ... WITH CSV QUOTE ''; but got a message that the quote string had to be at least one character. Any ideas? Best, --Lee -- Lee Hachadoorian PhD, Earth& Environmental Sciences (Geography) Research Associate, CUNY Center for Urban Research http://freecity.commons.gc.cuny.edu
Lee Hachadoorian <lee.hachadoorian@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
On 03/15/2012 12:23 PM, Tom Lane wrote: > Lee Hachadoorian<lee.hachadoorian@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