Oops forgot to cc the list. > Unfortunately, intra-row functions using nulls return nulls. Inter-row > functions "usually" ignore the nulls. I think there may be a few exceptions. > Though there is a relational theory which has is rigorously consistent, > nulls are not part of the theory. Nulls are basically what someone/somewhere > thought of as a convenient tool (which it is) but has no theoretical > underpinning and is dangerous. I use it because I feel that I have enough > experience but perhaps I'll be stung one day. > > It has been discussed on the list before that in Oracle that in Oracle an > empty string and null are the same. However Postgres treats an empty string > '' as an actual value and not as null. > > I just happened to notice another difference recently between Oracle and > Postgresql for the clause > > WHERE 1 IN (1,2,NULL) > > In Oracle, this clause is false because 1 compared to a NULL is false. > However, Postgresql will return a true. I actually don't know what the ANSI > standard is for this case. Perhaps someone else on this list will know. > Perhaps the standard body never even thought of this. Yes, I was actually > stung by this particular while using it in Oracle. > > Vincent Hikida, > Member of Technical Staff - Urbana Software, Inc. > "A Personalized Learning Experience" > > www.UrbanaSoft.com > > ----- Original Message ----- > From: "Jean-Christian Imbeault" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Sunday, July 13, 2003 10:42 PM > Subject: [GENERAL] select null + 0 question > > > > Why is it that "select null + 1" gives null but "select sum(a) from > > table" where there are null entries returns an integer? > > > > Shouldn't the sum() and "+" operators behave the same? > > > > TAL=# select null + 0; > > ?column? > > ---------- > > > > (1 row) > > > > TAL=# select * from a; > > a > > --- > > > > > > 1 > > (3 rows) > > > > TAL=# select sum(a) from a; > > sum > > ----- > > 1 > > (1 row) > > > > > > Thanks, > > > > Jean-Christian Imbeault > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 8: explain analyze is your friend > > >
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster