Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [206.210.65.6]) by hub.org (8.9.3/8.9.3) with ESMTP id JAA00170 for ; Wed, 19 May 1999 09:54:53 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id JAA29715; Wed, 19 May 1999 09:53:21 -0400 (EDT) To: "Robert Chalmers" cc: pgsql-sql@postgreSQL.org Subject: Re: [SQL] Why wont this update work? In-reply-to: Your message of Wed, 19 May 1999 14:49:30 +1000 <008d01bea1b2$fc0fd820$1a6001cb@chalmers.com.au> Date: Wed, 19 May 1999 09:53:20 -0400 Message-ID: <29713.927122000@sss.pgh.pa.us> From: Tom Lane "Robert Chalmers" writes: > UPDATE catalog > SET language = 'NoD' > WHERE language = 'NULL'; ........ or NULL and so on. > [ doesn't work ] WHERE language IS NULL is the SQL-standard-approved way of testing for NULL fields. (There's also IS NOT NULL if you need that.) If you have a sufficiently recent version of Postgres, it will also accept WHERE language = NULL (no quotes), but that's a nonstandard extension. (We only put it in because we had to for reasons of compatibility with Microsoft applications.) > UPDATE catalog > SET language = 'NoD' > WHERE NOT language = 'E'; This approach cannot work for finding nulls, because in expressions null essentially means "don't know" ... and that means it propagates. Is null equal to 'E'? The answer is "I don't know" --- another null. Feed that null to the NOT operator, and out comes another null. The buck stops at the WHERE clause, which is looking for a 'true' result ... but null isn't true either, so the tuple doesn't get selected. In short, no expression involving ordinary comparison and logic operators can produce anything but null when fed a null input. And that means you can't select tuples containing nulls with such an expression. This is why the standard decrees a special syntax for testing for null --- it's a very special operation. Microsoft's database code is in fact violating the standard by accepting field = NULL as meaning field IS NULL, because by rights field = NULL should always produce a null result (whether the field contains null or not!). regards, tom lane