pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcomparing nulls
9+ messages / 7 participants
[nested] [flat]
* comparing nulls
@ 2004-01-20 13:43 Kenneth Gonsalves <lawgon@thenilgiris.com>
2004-01-20 13:56 ` Re: comparing nulls Chris Bowlby <excalibur@hub.org>
2004-01-20 14:06 ` Re: comparing nulls D'Arcy J.M. Cain <darcy@druid.net>
2004-01-20 14:33 ` Re: comparing nulls Reinoud van Leeuwen <reinoud.v@n.leeuwen.net>
0 siblings, 3 replies; 9+ messages in thread
From: Kenneth Gonsalves @ 2004-01-20 13:43 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: comparing nulls
2004-01-20 13:43 comparing nulls Kenneth Gonsalves <lawgon@thenilgiris.com>
@ 2004-01-20 13:56 ` Chris Bowlby <excalibur@hub.org>
2004-01-20 14:24 ` Re: comparing nulls Kenneth Gonsalves <lawgon@thenilgiris.com>
2 siblings, 1 reply; 9+ messages in thread
From: Chris Bowlby @ 2004-01-20 13:56 UTC (permalink / raw)
To: Kenneth Gonsalves <lawgon@thenilgiris.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: comparing nulls
2004-01-20 13:43 comparing nulls Kenneth Gonsalves <lawgon@thenilgiris.com>
2004-01-20 13:56 ` Re: comparing nulls Chris Bowlby <excalibur@hub.org>
@ 2004-01-20 14:24 ` Kenneth Gonsalves <lawgon@thenilgiris.com>
2004-01-20 15:04 ` Re: comparing nulls Chris Bowlby <excalibur@hub.org>
2004-01-20 15:28 ` Re: comparing nulls Guy Fraser <guy@incentre.net>
2004-01-20 16:06 ` Re: comparing nulls Christopher Browne <cbbrowne@acm.org>
0 siblings, 3 replies; 9+ messages in thread
From: Kenneth Gonsalves @ 2004-01-20 14:24 UTC (permalink / raw)
To: chris@hub.org, Chris Bowlby <excalibur@hub.org>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: comparing nulls
2004-01-20 13:43 comparing nulls Kenneth Gonsalves <lawgon@thenilgiris.com>
2004-01-20 13:56 ` Re: comparing nulls Chris Bowlby <excalibur@hub.org>
2004-01-20 14:24 ` Re: comparing nulls Kenneth Gonsalves <lawgon@thenilgiris.com>
@ 2004-01-20 15:04 ` Chris Bowlby <excalibur@hub.org>
2 siblings, 0 replies; 9+ messages in thread
From: Chris Bowlby @ 2004-01-20 15:04 UTC (permalink / raw)
To: Kenneth Gonsalves <lawgon@thenilgiris.com>; +Cc: chris@hub.org; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: comparing nulls
2004-01-20 13:43 comparing nulls Kenneth Gonsalves <lawgon@thenilgiris.com>
2004-01-20 13:56 ` Re: comparing nulls Chris Bowlby <excalibur@hub.org>
2004-01-20 14:24 ` Re: comparing nulls Kenneth Gonsalves <lawgon@thenilgiris.com>
@ 2004-01-20 15:28 ` Guy Fraser <guy@incentre.net>
2004-01-20 15:53 ` Re: comparing nulls Jeff Eckermann <jeff_eckermann@yahoo.com>
2 siblings, 1 reply; 9+ messages in thread
From: Guy Fraser @ 2004-01-20 15:28 UTC (permalink / raw)
To: Kenneth Gonsalves <lawgon@thenilgiris.com>; +Cc: chris@hub.org, Chris Bowlby <excalibur@hub.org>; pgsql-sql
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 :-)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: comparing nulls
2004-01-20 13:43 comparing nulls Kenneth Gonsalves <lawgon@thenilgiris.com>
2004-01-20 13:56 ` Re: comparing nulls Chris Bowlby <excalibur@hub.org>
2004-01-20 14:24 ` Re: comparing nulls Kenneth Gonsalves <lawgon@thenilgiris.com>
2004-01-20 15:28 ` Re: comparing nulls Guy Fraser <guy@incentre.net>
@ 2004-01-20 15:53 ` Jeff Eckermann <jeff_eckermann@yahoo.com>
0 siblings, 0 replies; 9+ messages in thread
From: Jeff Eckermann @ 2004-01-20 15:53 UTC (permalink / raw)
To: Kenneth Gonsalves <lawgon@thenilgiris.com>; +Cc: pgsql-sql
--- 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: comparing nulls
2004-01-20 13:43 comparing nulls Kenneth Gonsalves <lawgon@thenilgiris.com>
2004-01-20 13:56 ` Re: comparing nulls Chris Bowlby <excalibur@hub.org>
2004-01-20 14:24 ` Re: comparing nulls Kenneth Gonsalves <lawgon@thenilgiris.com>
@ 2004-01-20 16:06 ` Christopher Browne <cbbrowne@acm.org>
2 siblings, 0 replies; 9+ messages in thread
From: Christopher Browne @ 2004-01-20 16:06 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: comparing nulls
2004-01-20 13:43 comparing nulls Kenneth Gonsalves <lawgon@thenilgiris.com>
@ 2004-01-20 14:06 ` D'Arcy J.M. Cain <darcy@druid.net>
2 siblings, 0 replies; 9+ messages in thread
From: D'Arcy J.M. Cain @ 2004-01-20 14:06 UTC (permalink / raw)
To: Kenneth Gonsalves <lawgon@thenilgiris.com>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: comparing nulls
2004-01-20 13:43 comparing nulls Kenneth Gonsalves <lawgon@thenilgiris.com>
@ 2004-01-20 14:33 ` Reinoud van Leeuwen <reinoud.v@n.leeuwen.net>
2 siblings, 0 replies; 9+ messages in thread
From: Reinoud van Leeuwen @ 2004-01-20 14:33 UTC (permalink / raw)
To: pgsql-sql
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
__________________________________________________
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2004-01-20 16:06 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-20 13:43 comparing nulls Kenneth Gonsalves <lawgon@thenilgiris.com>
2004-01-20 13:56 ` Chris Bowlby <excalibur@hub.org>
2004-01-20 14:24 ` Kenneth Gonsalves <lawgon@thenilgiris.com>
2004-01-20 15:04 ` Chris Bowlby <excalibur@hub.org>
2004-01-20 15:28 ` Guy Fraser <guy@incentre.net>
2004-01-20 15:53 ` Jeff Eckermann <jeff_eckermann@yahoo.com>
2004-01-20 16:06 ` Christopher Browne <cbbrowne@acm.org>
2004-01-20 14:06 ` D'Arcy J.M. Cain <darcy@druid.net>
2004-01-20 14:33 ` Reinoud van Leeuwen <reinoud.v@n.leeuwen.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox