Thread: \copy ... with null as '' csv doesn't get nulls
I'd expect this:
\copy tablename from 'filename.csv' WITH NULL as '' CSV HEADER
whould import "" as NULL.
The input file is UTF-8 (not Unicode/UTF-16).
I checked the hexdump and the "wannabe" NULL are actually
2c 22 22 2c -> ,"",
all fields are varchar that admit NULL
but still I get empty strings ('') in spite of NULL.
Am I missing something or it is a well known "feature"?
thx
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
Ivan Sergio Borgonovo wrote:
> I'd expect this:
>
> \copy tablename from 'filename.csv' WITH NULL as '' CSV HEADER
>
> whould import "" as NULL.
>
> The input file is UTF-8 (not Unicode/UTF-16).
> I checked the hexdump and the "wannabe" NULL are actually
> 2c 22 22 2c -> ,"",
>
> all fields are varchar that admit NULL
>
> but still I get empty strings ('') in spite of NULL.
>
> Am I missing something or it is a well known "feature"?
I went to the docs for COPY and they say,
> The default is \N (backslash-N) in text mode, and a empty value with no quotes in CSV mode.
That "with no quotes" phrase caught my attention.
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.
--
Lew
On Sun, 25 Nov 2007 13:22:48 -0500 Lew <lew@lwsc.ehost-services.com> wrote: > I went to the docs for COPY and they say, > > The default is \N (backslash-N) in text mode, and a empty value > > with no quotes in CSV mode. > That "with no quotes" phrase caught my attention. > 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. Exactly what I did because fortunately there weren't too many chances of weird stuff in 2000 records (sed -e 's/,""/,/g'). Anyway with NULL AS '' and without it I can't still import NULL without 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". thx -- Ivan Sergio Borgonovo http://www.webthatworks.it
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? > Anyway with NULL AS '' and without it I can't still import NULL > without pre-processing. What pre-processing is that? > 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. -- Lew This post contained two requests for responses.
On Tue, 27 Nov 2007 21:12:00 -0500 Lew <lew@lwsc.ehost-services.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 "''", '""', '\"\"'... -- Ivan Sergio Borgonovo http://www.webthatworks.it
On Thursday 29 November 2007 2:40 pm, Ivan Sergio Borgonovo wrote: > On Tue, 27 Nov 2007 21:12:00 -0500 > > Lew <lew@lwsc.ehost-services.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@comcast.net