Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f4FNvKA49144 for ; Tue, 15 May 2001 19:57:20 -0400 (EDT) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.1/8.11.1) id f4FNanh66357 for pgsql-sql@postgresql.org; Tue, 15 May 2001 19:36:49 -0400 (EDT) (envelope-from news) From: Per-Olof Pettersson X-Newsgroups: comp.databases.postgresql.sql Subject: Re: How NULL is interpreted in Pgsql Date: Tue, 15 May 2001 23:38:22 GMT Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 91 Message-ID: <20010515.23382236@mis.configured.host> References: Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 X-Complaints-To: scrappy@hub.org X-Priority: 3 (Normal) X-Newsreader: Mozilla/3.0 (compatible; StarOffice/5.2;Win32) To: pgsql-sql@postgresql.org Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by postgresql.org id f4FNvKA49147 X-Archive-Number: 200105/156 X-Sequence-Number: 2973 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)