[SQL] comparing nulls

[Kenneth Gonsalves]

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

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] comparing nulls

[Chris Bowlby]

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 <[EMAIL PROTECTED]>
Hub.Org Networking Services


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] comparing nulls

[D'Arcy J.M. Cain]

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 <[EMAIL PROTECTED]|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.

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [SQL] comparing nulls

[Reinoud van Leeuwen]

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[EMAIL PROTECTED]
http://www.xs4all.nl/~reinoud
__

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] comparing nulls

[Kenneth Gonsalves]

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

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] comparing nulls

[Chris Bowlby]

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 <[EMAIL PROTECTED]>
Hub.Org Networking Services


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] comparing nulls

[Guy Fraser]

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 :-)



---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

Re: [SQL] comparing nulls

[Jeff Eckermann]

--- Guy Fraser <[EMAIL PROTECTED]> 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

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] comparing nulls

[Christopher Browne]

Centuries ago, Nostradamus foresaw when [EMAIL PROTECTED] (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,  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

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match