pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
How NULL is interpreted in Pgsql
3+ messages / 3 participants
[nested] [flat]

* How NULL is interpreted in Pgsql
@ 2001-05-15 08:08  Guru Prasad <pnguruji@yahoo.com>
  0 siblings, 2 replies; 3+ messages in thread

From: Guru Prasad @ 2001-05-15 08:08 UTC (permalink / raw)
  To: pgsql-sql


Dear Friends,

I created a table with no 'unique constraints' which is depicted as
follows.

create table junk (id int, name varchar);

Then, i inserted some records into the table. My table contents is listed
below.

     id      | name
-------------+------
 1001.000000 | bolt
 1002.000000 | nut
 1003.000000 |
   11.000000 | screw
             | cutter
             | cutting table

Then, i gave a select query for the above table. The select query is

'select * from junk where id != 11';

To my surprise, it displayed all the records except those which had
'NULL' in the 'id' field.

How is that possible ? (I mean it should displayed all the records except
the matching condition). 

But, if i set PRIMARY KEY constraint to 'junk' table then it displays all
the records (I can't say NULL here, but i can say '0'). 

How NULL is processed in Postgres. 
 
Could any one has some clue.

Regards,
guru.

bk SYSTEMS (P) Ltd.
                                                    P . N . G U R U P R A S A D
-------------------------------------------------------------------------------






^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: How NULL is interpreted in Pgsql
@ 2001-05-15 14:12  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Guru Prasad <pnguruji@yahoo.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Tom Lane @ 2001-05-15 14:12 UTC (permalink / raw)
  To: Guru Prasad <pnguruji@yahoo.com>; +Cc: pgsql-sql

Guru Prasad <pnguruji@yahoo.com> writes:
> 'select * from junk where id != 11';
> To my surprise, it displayed all the records except those which had
> 'NULL' in the 'id' field.

This is correct per spec: NULL is not a value and it doesn't act like one.
See any discussion of SQL NULLs --- Bruce's book talks about this IIRC,
or we've been over the turf more than once in the mailing list archives.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: How NULL is interpreted in Pgsql
@ 2001-05-15 23:38  Per-Olof Pettersson <pgsql@peope.net>
  parent: Guru Prasad <pnguruji@yahoo.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Per-Olof Pettersson @ 2001-05-15 23:38 UTC (permalink / raw)
  To: pgsql-sql

Hi

In the condition "where id != 11" you state that the value should differ 
from null.
For a value to be different than 11 it has to be a value in the first 
place.

So in reality you state two things:
1. id must be a value
2. id must differ from 11

It is nothing specific with Postgresql but common to most(all) relational 
databases using SQL.

To get the desired result you can add "OR id IS NULL".

Result:
select * from junk where id != 11 OR id IS NULL;


Best regards
Per-Olof Pettersson

>>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<

On 2001-05-15, 10:16:22, pnguruji@yahoo.com (Guru Prasad) wrote regarding 
How NULL is interpreted in Pgsql:


> Dear Friends,

> I created a table with no 'unique constraints' which is depicted as
> follows.

> create table junk (id int, name varchar);

> Then, i inserted some records into the table. My table contents is listed
> below.

>      id      | name
> -------------+------
>  1001.000000 | bolt
>  1002.000000 | nut
>  1003.000000 |
>    11.000000 | screw
>              | cutter
>              | cutting table

> Then, i gave a select query for the above table. The select query is

> 'select * from junk where id != 11';

> To my surprise, it displayed all the records except those which had
> 'NULL' in the 'id' field.

> How is that possible ? (I mean it should displayed all the records except
> the matching condition).

> But, if i set PRIMARY KEY constraint to 'junk' table then it displays all
> the records (I can't say NULL here, but i can say '0').

> How NULL is processed in Postgres.

> Could any one has some clue.

> Regards,
> guru.

> bk SYSTEMS (P) Ltd.
>                                                     P . N . G U R U P R A 
S A D
> 
-------------------------------------------------------------------------------




> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2001-05-15 23:38 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-05-15 08:08 How NULL is interpreted in Pgsql Guru Prasad <pnguruji@yahoo.com>
2001-05-15 14:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-05-15 23:38 ` Per-Olof Pettersson <pgsql@peope.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