Guy Rouillier <guyr-ml1(at)burntmail(dot)com> wrote:
> Kevin Grittner wrote:
>> A failing of the SQL standard is that it uses the same mark (NULL)
>> to show the absence of a value because it is unknown as for the
>> case where it is known that no value exists (not applicable). Codd
>> argued for a distinction there, but it hasn't come to pass, at
>> least in the standard. If anyone could suggest a way to support
>> standard syntax and semantics and add extensions to support this
>> distinction, it might be another advance that would distinguish
>> PostgreSQL from "less evolved" products. :-)
>
> Theoretically, the distinction already exists. If you don't know a
> person's middle initial, then set it to null; if you know the
> person doesn't have one, set it to the empty string.
Well, it is arguable whether an empty string is the proper way to
indicate that a character string based column is not applicable to a
given row, but it certainly falls flat for any other types, such as
dates or numbers; and I think there's value in having a consistent way
to handle this.
> But from a practical point of view, that wouldn't go very far.
> Most *people* equate an empty string to mean the same as null. When
> I wrote my own data access layer years ago, I expressly checked for
> empty strings on input and changed them to null. I did this because
> empty strings had a nasty way of creeping into our databases;
> writing queries to produce predictable results got to be very messy.
Yeah, there's that, too.
Which leaves the issue open -- a flexible way to flag the *reason* (or
*reasons*) for the absence of a value could be a nice enhancement, if
someone could invent a good implementation. Of course, one could
always add a column to indicate the reason for a NULL; and perhaps
that would be as good as any scheme to attach reason flags to NULL.
You'd just have to make sure the reason column was null capable for
those rows where there *was* a value, which would make the reason "not
applicable"....
-Kevin