Re: Query plan for NOT IN - Mailing list pgsql-performance

From Kevin Grittner
Subject Re: Query plan for NOT IN
Date
Msg-id 4ACC94DF020000250002B6CE@gw.wicourts.gov
Whole thread Raw
In response to Re: Query plan for NOT IN  (Guy Rouillier <guyr-ml1@burntmail.com>)
Responses Re: Query plan for NOT IN  (Craig James <craig_james@emolecules.com>)
List pgsql-performance
Guy Rouillier <guyr-ml1@burntmail.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

pgsql-performance by date:

Previous
From: Guy Rouillier
Date:
Subject: Re: Query plan for NOT IN
Next
From: Craig James
Date:
Subject: Re: Query plan for NOT IN