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

Bruce Momjian wrote:

>I see that the default NULL for CSV mode is ''.  I was hoping the
>default was something more special.  Right now, by default, comma-comma
>is a null and comma-double-quote-double-quote-comma is a zero-length
>string.   I am thinking there should be a way to set NULL to be either
>of those, or neither of those, in which case comma-comma is a
>zero-length string too.
>
>To me, these characteristics are a property of the file, not of the
>individual fields.
>
>For example, WITH NULL BOTH would allow ,, and ,"", to both be null,
>
>

I can't see a real world use for this setting. And I think it would
break the property of the patch as it currently stands, that we can
unambiguously import what we exported, no matter what the settings. I
don't think we should abandon that lightly. Quite apart from any other
reason because it makes testing easier (just compare what you wrote with
what you read back).


>while using WITH NULL NONE, both ,, and ,"", are zero-length strings.
>
>

Again, I think this will break that property. But if that's what it
takes to be able to import to a table with NOT NULL in at least some
cases I could live with it. Just. But in the general case it won't work.
Say you are importing into a table with the following defn: (a text, b
text not null, c int). then the line 'x,,' will fail on b if '' is null,
and will fail on c if '' is empty string. And yet this sort of output is
exactly what is to be expected from a spreadsheet.

>And, finally, the default is WITH NULL STRICT (or SOME) where ,, is NULL
>and ,"", is the zero-length string.
>
>

That's what happens now with the default.

>Those are all existing keywords, and those special NULL values would
>only be available in CSV mode.
>
>I am not sure what NULL '' should so in these cases. I am thinking we
>would actually disable it for CSV mode because you would need to define
>which '' you are talking about.
>
>If you specify an actual string for NULL like WITH NULL 'fred', then
>both ,, and ,"", are zero-length strings, I think.
>
>

I don't believe '' should be special, any more than 'fred' should be. As
it stands now, NULL 'fred' does say that ,, and '"", are empty strings.

>Again, I can assist in making these modifications to the patch.
>
>

I appreciate your efforts. But as indicated elsewhere, right now I'm
leaning towards reworking this into a client, because the road seems to
be blocked on doing what I regard as necessary in the backend.

cheers

andrew