Re: String Comparison and NULL

From: seijin(at)gmail(dot)com pgsql-general(at)postgresql(dot)org Re: String Comparison and NULL 2008-04-29 18:08:21 c7823064-d03b-441f-82ec-9eb7a1fe6135@2g2000hsn.googlegroups.com (view raw or flat) 2008-04-28 16:39:24 from seijin(at)gmail(dot)com  2008-04-29 11:42:13 from "Pavel Stehule"   2008-04-29 12:44:07 from Andreas Kretschmer    2008-04-29 14:36:53 from Tom Lane     2008-04-29 18:08:21 from seijin(at)gmail(dot)com  2008-04-29 14:30:44 from Stephan Szabo pgsql-general
```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!

```

pgsql-general by date

 Next: From: Joseph S Date: 2008-04-29 18:52:39 Subject: Why is postgres autovacuuming a table that is never updated? Previous: From: Shane Ambler Date: 2008-04-29 16:34:56 Subject: Re: Help! ERROR: could not open relation