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
