Re: Updated COPY CSV patch

Bruno Wolff III wrote:

>On Tue, Apr 13, 2004 at 06:58:24 -0400,
>  Andrew Dunstan <andrew@dunslane.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)