Guy Rouillier <guyr-ml1@xxxxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
