Thread: Null comparison
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? 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 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. thanks, Al pls cc me on your response.
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.
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/ ------------