pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Lee Hachadoorian <lee.hachadoorian@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: PostgreSQL (SQL) <pgsql-sql@postgresql.org>
Subject: Re: COPY without quoting
Date: Thu, 15 Mar 2012 12:52:45 -0400
Message-ID: <4F621E5D.2050206@gmail.com> (raw)
In-Reply-To: <21644.1331828614@sss.pgh.pa.us>
References: <4F6215CE.7050207@gmail.com>
	<21644.1331828614@sss.pgh.pa.us>

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




view thread (3+ messages)

Message-ID: <4F621E5D.2050206@gmail.com>
Permalink:  ../4F621E5D.2050206@gmail.com/
Also on:    postgresql.org/message-id/4F621E5D.2050206@gmail.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: lee.hachadoorian@gmail.com, tgl@sss.pgh.pa.us
  Subject: Re: COPY without quoting
  In-Reply-To: <4F621E5D.2050206@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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