Skip site navigation (1) Skip section navigation (2)

Re: Updated COPY CSV patch

From: Andrew Dunstan <andrew(at)dunslane(dot)net>
To: pgsql-patches(at)postgresql(dot)org
Subject: Re: Updated COPY CSV patch
Date: 2004-04-13 19:54:59
Message-ID: 407C4593.4010207@dunslane.net (view raw or flat)
Thread:
Lists: 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









In response to

Responses

pgsql-patches by date

Next:From: LELARGE GuillaumeDate: 2004-04-13 21:37:58
Subject: Re: Update french translation of the 7.4 branch
Previous:From: Tom LaneDate: 2004-04-13 18:55:10
Subject: Re: Updated COPY CSV patch

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group