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 16:27:54
Message-ID: 407C150A.90908@dunslane.net (view raw or flat)
Thread:
Lists: pgsql-patches
Bruno Wolff III wrote:

>On Tue, Apr 13, 2004 at 06:58:24 -0400,
>  Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>  
>
>>One area that we should think about as an enhancement is NOT NULL fields.
>>As it stands now, we will get what we normally get when we try to insert
>>a  null into a NOT NULL field, namely an error. If the field has a simple
>>literal default we could force that. And in the special case of
>>text/varchar fields, it would be reasonable to force an empty string even
>>if no default is set. There isn't a nice easy answer, I'm afraid. We
>>shouldn't hold up putting this in on that account, but handling this
>>better is certainly a TODO.
>>    
>>
>
>If you try to insert NULLs into a nonnull field you should get an error.
>If you have unquoted empty strings, and are not using the empty string as 
>the NULL marker, then you can just not set the NULL code to be the empty
>string. If you need to turn this on and off by column, then perhaps it
>would be better to do that externally.
>
>As for setting default values, I think that is a good idea. I suggested
>a while back. There could be another keyword, DEFAULT, on the COPY FROM
>command that is used to define a code that will be replaced by the
>default value (or NULL if there is no default for a column) similar to
>how the NULL code is replaced by NULL.
>  
>

Well, as I indicated we can deal with this in a subsequent round, I 
think. However, here's an idea. We know (or can easily discover) if 
there is a NOT NULL constraint that can apply to the attribute (or 
domain if it is a domain type). If isnull is set on the read-in value in 
such a case, instead of trying to insert null, and knowing we would 
fail, try to insert the value we actually read (usually ''), even though 
we think we found a null. This will succeed with text fields, and fail 
with, for example, int fields. That strikes me as quite reasonable 
behaviour, although perhaps qualifying for a warning. Or perhaps we 
could enable such behaviour with a flag.

Of course, this would be for CSV mode only - standard TEXT mode should 
work as now.

cheers

andrew (trying to be creative here)

In response to

Responses

pgsql-patches by date

Next:From: Bruce MomjianDate: 2004-04-13 17:26:47
Subject: Re: Updated COPY CSV patch
Previous:From: Andrew DunstanDate: 2004-04-13 15:36:36
Subject: Re: Updated COPY CSV patch

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