From: Per-Olof Pettersson <pgsql@peope.net>
To: pgsql-sql@postgresql.org
Subject: Re: How NULL is interpreted in Pgsql
Date: Tue, 15 May 2001 23:38:22 GMT
Message-ID: <20010515.23382236@mis.configured.host> (raw)
References: <Pine.LNX.4.20.0105151323560.16374-100000@Larry.bks>
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pgsql@peope.net
Subject: Re: How NULL is interpreted in Pgsql
In-Reply-To: <20010515.23382236@mis.configured.host>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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