Re: Updated COPY CSV patch - Mailing list pgsql-patches

From Andrew Dunstan
Subject Re: Updated COPY CSV patch
Date
Msg-id 407BF6CD.3000005@dunslane.net
Whole thread Raw
In response to Re: Updated COPY CSV patch  ("Andrew Dunstan" <andrew@dunslane.net>)
Responses Re: Updated COPY CSV patch  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-patches
Andrew Dunstan wrote:

>Bruce Momjian said:
>
>
>>Second, I found a problem with NULLs.  If I do:
>>.
>>       test=> create table test (x text, y text);
>>       CREATE TABLE
>>       test=> insert into test values ('', NULL);
>>       INSERT 17221 1
>>       test=>
>>
>>then this:
>>
>>       test=> copy test to '/tmp/b' with csv;
>>
>>creates:
>>
>>       "",
>>
>>and this:
>>
>>       test=> copy test to '/tmp/b' with csv NULL 'fred';
>>
>>creates:
>>
>>       ,fred
>>
>>Is that logical?  A non-null field went from "" to nothing.
>>
>>
>>
>
>One more point about this - we can't force quoting of every non-null
>value, which would remove the "inconsistency" you see here, because
>spreadsheets especially infer information from whether or not a CSV value
>is quoted. In particular, they will not usually treat a quoted numeric
>value as numeric, which would be a very undesirable effect.
>
>
>

Thinking about this some more .... maybe the right rule would be "quote
all non-numeric non-null values". That would fix the odd effect that
Bruce saw, and it would also stop a spreadsheet from interpreting a date
like 2002-03-04 as an arithmetic expression.

Note that we don't care if a value is quoted or not - the only inference
we draw from it is that if it is quoted it can't be null. We don't need
to draw type inferences from it because we know the type we are trying
to import into from the table defn. This fits nicely with the rule about
being liberal with what you accept.

Thoughts?

cheers

andrew

pgsql-patches by date:

Previous
From: Bruno Wolff III
Date:
Subject: Re: Updated COPY CSV patch
Next
From: Tom Lane
Date:
Subject: Re: Updated COPY CSV patch