pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCOPY without quoting
3+ messages / 2 participants
[nested] [flat]
* COPY without quoting
@ 2012-03-15 16:16 Lee Hachadoorian <lee.hachadoorian@gmail.com>
2012-03-15 16:23 ` Re: COPY without quoting Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Lee Hachadoorian @ 2012-03-15 16:16 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: COPY without quoting
2012-03-15 16:16 COPY without quoting Lee Hachadoorian <lee.hachadoorian@gmail.com>
@ 2012-03-15 16:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-03-15 16:52 ` Re: COPY without quoting Lee Hachadoorian <lee.hachadoorian@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2012-03-15 16:23 UTC (permalink / raw)
To: Lee Hachadoorian <lee.hachadoorian@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: COPY without quoting
2012-03-15 16:16 COPY without quoting Lee Hachadoorian <lee.hachadoorian@gmail.com>
2012-03-15 16:23 ` Re: COPY without quoting Tom Lane <tgl@sss.pgh.pa.us>
@ 2012-03-15 16:52 ` Lee Hachadoorian <lee.hachadoorian@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Lee Hachadoorian @ 2012-03-15 16:52 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2012-03-15 16:52 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-03-15 16:16 COPY without quoting Lee Hachadoorian <lee.hachadoorian@gmail.com>
2012-03-15 16:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-03-15 16:52 ` Lee Hachadoorian <lee.hachadoorian@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox