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

Andrew Dunstan wrote:
> >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
> behavior, although perhaps qualifying for a warning. Or perhaps we
> could enable such behavior with a flag.
>
> Of course, this would be for CSV mode only - standard TEXT mode should
> work as now.

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,
while using WITH NULL NONE, both ,, and ,"", are zero-length strings.
And, finally, the default is WITH NULL STRICT (or SOME) where ,, is NULL
and ,"", is the zero-length string.

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.

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

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073