Received: from comptechnews.com (cc993546-b.srst1.fl.home.com [24.3.77.52]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eBE3VAX81889 for ; Wed, 13 Dec 2000 22:31:10 -0500 (EST) (envelope-from reaster@comptechnews.com) Received: from comptechnews (reaster@localhost [127.0.0.1]) by comptechnews.com (8.10.2/8.10.2) with SMTP id eBE3c7K26704; Wed, 13 Dec 2000 22:38:11 -0500 From: "Robert B. Easter" Date: Wed, 13 Dec 2000 22:38:07 -0500 X-Mailer: KMail [version 1.1.99] Content-Type: text/plain; charset="US-ASCII" Cc: pgsql-sql@postgresql.org To: Stephan Szabo , Al Lewis References: In-Reply-To: Subject: Re: Null comparison MIME-Version: 1.0 Message-Id: <00121322380726.00289@comptechnews> Content-Transfer-Encoding: 8bit X-Archive-Number: 200012/136 X-Sequence-Number: 470 I wrote some notes on three-valued logic (true,false,unknown) at my website: http://www.comptechnews.com/~reaster/dbdesign.html#three-valued-logic If anyone finds errors with it, please let me know. On Wednesday 13 December 2000 19:03, Stephan Szabo wrote: > On Wed, 13 Dec 2000, Al Lewis wrote: > > I am migrating to postgress from msql and am encountering numerous > > problems in the differences in NULL usage and comparison. > > > > 1. Why are 2 fields not equal if they are both NULL? > > Because that's what the SQL spec says. If either value is NULL > the result is unknown (because NULL is not a value really, > is this unknown value equal to some other unknown value...). > > > 2. Is there a way to easily modify my sql select statements to account > > for either column being NULL and having them return true if they both are > > NULL? select a.name, b.cost from a, b where a.type=b.type > > where a.type=b.type or (a.type is null and b.type is null) > should do it. > > > I'd like to make this as easy as possible so I can put it into a > > "translation" function. Currently I have a regsub that handles <> and > > NULLs, since <> doesn't work on a NULL field. -- -------- Robert B. Easter reaster@comptechnews.com --------- - CompTechNews Message Board http://www.comptechnews.com/ - - CompTechServ Tech Services http://www.comptechserv.com/ - ---------- http://www.comptechnews.com/~reaster/ ------------