Skip site navigation (1) Skip section navigation (2)

Re: String Comparison and NULL

From: seijin(at)gmail(dot)com
To: pgsql-general(at)postgresql(dot)org
Subject: Re: String Comparison and NULL
Date: 2008-04-29 18:08:21
Message-ID: c7823064-d03b-441f-82ec-9eb7a1fe6135@2g2000hsn.googlegroups.com (view raw or flat)
Thread:
Lists: 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!

In response to

pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group