On Tue, Apr 13, 2004 at 06:58:24 -0400,
Andrew Dunstan <[EMAIL PROTECTED]> 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)
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
