Thread: comparing nulls
in postgres7.1 i had a table where an integer field could be null. There was no default value. a select statement like so: 'select * from table where field = null' would give all the rows where that field had no value. on porting to 7.3.2, this doesnt work. How to do this? -- regards kg http://www.ootygolfclub.org poor man's tally: http://avsap.sourceforge.net
Hi Ken, Under 7.3.x this option was removed, you need to test via: SELECT * from table where field IS NULL; On Tue, 2004-01-20 at 09:43, Kenneth Gonsalves wrote: > in postgres7.1 i had a table where an integer field could be null. There was > no default value. a select statement like so: > 'select * from table where field = null' > would give all the rows where that field had no value. > on porting to 7.3.2, this doesnt work. How to do this? -- Chris Bowlby <excalibur@hub.org> Hub.Org Networking Services
On January 20, 2004 08:43 am, Kenneth Gonsalves wrote:
> in postgres7.1 i had a table where an integer field could be null. There
> was no default value. a select statement like so:
> 'select * from table where field = null'
> would give all the rows where that field had no value.
> on porting to 7.3.2, this doesnt work. How to do this?
As per the SQL standard:
SELECT * FROM table WHERE field IS NOT NULL;
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
On Tue, Jan 20, 2004 at 07:13:12PM +0530, Kenneth Gonsalves wrote: > in postgres7.1 i had a table where an integer field could be null. There was > no default value. a select statement like so: > 'select * from table where field = null' > would give all the rows where that field had no value. > on porting to 7.3.2, this doesnt work. How to do this? Because NULL can be read as "unknown". It does not make much sense to test wheter a value of an integer is numerically equal to unknown. That is why there is the IS operator: where field IS null -- __________________________________________________ "Nothing is as subjective as reality" Reinoud van Leeuwen reinoud.v@n.leeuwen.net http://www.xs4all.nl/~reinoud __________________________________________________
On Tuesday 20 January 2004 19:26, Chris Bowlby wrote: > Hi Ken, > > Under 7.3.x this option was removed, you need to test via: > > SELECT * from table where field IS NULL; thanx - works in both 7.1 and 7.3 - why do these guys keep fooling around with these thangs? -- regards kg http://www.ootygolfclub.org poor man's tally: http://avsap.sourceforge.net
To achieve a higher level of SQL compliancy.. On Tue, 2004-01-20 at 10:24, Kenneth Gonsalves wrote: > On Tuesday 20 January 2004 19:26, Chris Bowlby wrote: > > Hi Ken, > > > > Under 7.3.x this option was removed, you need to test via: > > > > SELECT * from table where field IS NULL; > thanx - works in both 7.1 and 7.3 - why do these guys keep fooling around > with these thangs? -- Chris Bowlby <excalibur@hub.org> Hub.Org Networking Services
Kenneth Gonsalves wrote: >On Tuesday 20 January 2004 19:26, Chris Bowlby wrote: > > >>Hi Ken, >> >> Under 7.3.x this option was removed, you need to test via: >> >> SELECT * from table where field IS NULL; >> >> >thanx - works in both 7.1 and 7.3 - why do these guys keep fooling around >with these thangs? > > Standards compliance :-)
--- Guy Fraser <guy@incentre.net> wrote: > Kenneth Gonsalves wrote: > > >On Tuesday 20 January 2004 19:26, Chris Bowlby > wrote: > > > > > >>Hi Ken, > >> > >> Under 7.3.x this option was removed, you need to > test via: > >> > >> SELECT * from table where field IS NULL; > >> > >> > >thanx - works in both 7.1 and 7.3 - why do these > guys keep fooling around > >with these thangs? > > > > > Standards compliance :-) You can get the old behaviour by setting "transform_null_equals" to true in postgresql.conf. I believe that this was originally added to add compatibility with MS Access. But Access no longer requires this, and the developers were not interested in maintaining a non-spec-compliant behaviour. But the postgresql.conf setting was retained for those who have code that relies on that behaviour. __________________________________ Do you Yahoo!? Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus
Centuries ago, Nostradamus foresaw when lawgon@thenilgiris.com (Kenneth Gonsalves) would write: > On Tuesday 20 January 2004 19:26, Chris Bowlby wrote: >> Under 7.3.x this option was removed, you need to test via: >> >> SELECT * from table where field IS NULL; > thanx - works in both 7.1 and 7.3 - why do these guys keep fooling around > with these thangs? Because there is a desire to have PostgreSQL conform with public standards such as SQL-1999. The use of "IS NULL" conforms with SQL standards; the use of "= NULL" does not. -- If this was helpful, <http://svcs.affero.net/rm.php?r=cbbrowne> rate me http://www3.sympatico.ca/cbbrowne/advocacy.html "Let's face it -- ASCII text is a far richer medium than most of us deserve." -- Scott McNealy