Re: How to select rows with values set to NULL

Hi,

> Seems to work here:
>
> jaa=# create table test2(id int, txt varchar(17) default NULL);
> CREATE

This works, but you don't need to specify 'default NULL', that is the
default anyway...

> jaa=# insert into test2 values(1);
> INSERT 420453 1
>
> jaa=# select * from test2 where txt = null;

This is _not_ the right way to look for NULL values! Use the following
expression:
select * from test2 where txt IS NULL;

This is explained on
http://www2.nl.postgresql.org/users-lounge/docs/7.2/postgres/functions-compa
rison.html:

--- start quote ---
Do not write expression = NULL because NULL is not "equal to" NULL. (NULL
represents an unknown value, and it is not known whether two unknown values
are equal.)

Some applications may (incorrectly) require that expression = NULL returns
true if expression evaluates to the NULL value. To support these
applications, the run-time option transform_null_equals can be turned on
(e.g., SET transform_null_equals TO ON;). PostgreSQL will then convert x =
NULL clauses to x IS NULL. This was the default behavior in releases 6.5
through 7.1.
--- end quote ---

Good luck,
Sander.