From: Tom Lane <tgl@sss.pgh.pa.us>
To: Robert Chalmers <robert@chalmers.com.au>
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] Why wont this update work?
Date: Wed, 19 May 1999 09:53:20 -0400
Message-ID: <29713.927122000@sss.pgh.pa.us> (raw)
In-Reply-To: <008d01bea1b2$fc0fd820$1a6001cb@chalmers.com.au>
"Robert Chalmers" <robert@chalmers.com.au> 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
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, robert@chalmers.com.au
Subject: Re: [SQL] Why wont this update work?
In-Reply-To: <29713.927122000@sss.pgh.pa.us>
References: <008d01bea1b2$fc0fd820$1a6001cb@chalmers.com.au> <29713.927122000@sss.pgh.pa.us>
* 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