Re: String Comparison and NULL

On Apr 29, 7:36 am, t(dot)(dot)(dot)(at)sss(dot)pgh(dot)pa(dot)us (Tom Lane) wrote:
> Andreas Kretschmer <akretsch(dot)(dot)(dot)(at)spamfence(dot)net> writes:
> >>> ... and I do something like "select id where animal <> 'Cat';" then
> >>> shouldn't 1, 3, 4 and 5 be picked? As it is I only get 1, 4 and 5.
> >>> NULL is not 'Cat'. I realize that if I were testing for NULL itself I
> > NULL is nothing, you can't compare something with nothing.
>
> A better way to think about it is that NULL means UNKNOWN. Thus
> the result of NULL <> 'Cat' is not FALSE but UNKNOWN (ie NULL)
> --- if you don't know what the value is, you don't know whether or not
> it's equal to any specific other value.
>
> The other mistake novices typically make is to expect that
> NULL = NULL will yield TRUE. It doesn't, it yields NULL,
> because again you can't say whether two unknown quantities
> are equal.
>
> You can hack around this behavior to some extent with
> IS DISTINCT FROM, but generally the right thing is to redesign
> your data representation. Trying to make NULL act like a normal
> data value is almost always going to lead to tears in the long run.
>
> regards, tom lane
>
> --
> Sent via pgsql-general mailing list (pgsql-gene(dot)(dot)(dot)(at)postgresql(dot)org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general

This is a lot of good information and really helps. I think I'll
rework my DB design and client program to try and avoid this behavior
all together. I'm sure that'll be the best option in the long run.

Thanks to everyone for the help!