Re: \copy ... with null as '' csv doesn't get nulls

On Thursday 29 November 2007 2:40 pm, Ivan Sergio Borgonovo wrote:
> On Tue, 27 Nov 2007 21:12:00 -0500
>
> Lew <lew(at)lwsc(dot)ehost-services(dot)com> wrote:
> > Lew wrote:
> > >> Try eliminating the double quotes in the CSV file. "Wannabe"
> > >> NULL would then be ,, (consecutive commas)
> > >> in the CSV. From the docs, you don't even need the "NULL AS"
> > >> clause in your COPY statement.
> >
> > Ivan Sergio Borgonovo wrote:
> > > Exactly what I did because fortunately there weren't too many
> > > chances of weird stuff in 2000 records (sed -e 's/,""/,/g').
> >
> > And this worked, right?
>
> right and I call it pre-processing.
>
> > > I thought it may be missing total support of UTF-8 or if I did
> > > something wrong or it is actually a "feature".
> >
> > This clearly has nothing to do with UTF-8, and everything to with
> > comma-comma representing a NULL and comma-quote-quote-comma
> > representing an empty string.
>
> OK... misinterpreted.
>
> I thought that NULL AS '' means ,'',[1] so that empty strings could be
> imported as NULL if necessary and as empty string if not.
>
> So at my understanding there is no way to use \copy and insert NULL
> when it encounter an empty string and NULL AS '' doesn't do anything
> in CSV mode since when I have ,, it actually imports NULL and when I
> have ,'', it imports empty strings that is the same behaviour I get
> without NULL AS ''.
>
> Correct?
> If it is I found the documentation a bit misleading. I admit it could
> be due to not being English mother tongue.
>
> thx
>
> [1] I did try with "''", '""', '\"\"'...

I tried this with 8.2 on a test case. To get it to work I needed to escape the
quotes:
\copy tablename from 'filename.csv' WITH NULL as E'\'\'' CSV HEADER
This uses the new escape string syntax in 8.2. With versions before I believe
the following would work:
\copy tablename from 'filename.csv' WITH NULL as '\'\'' CSV HEADER

--
Adrian Klaver
aklaver(at)comcast(dot)net