Re: How to select rows with values set to NULL

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Jani Averbach
> Sent: Thursday, May 23, 2002 5:05 AM
> To: Tille, Andreas
> Cc: PostgreSQL General
> Subject: Re: [GENERAL] How to select rows with values set to NULL
>
>
> On Thu, 23 May 2002, Tille, Andreas wrote:
>
> jaa=# select * from test2 where txt = null;
> id | txt
> ----+-----
> 1 |
> (1 row)

> >
> > InfluenzaWeb=# select * from test where txt = NULL;
> > id | txt
> > ----+-----
> > (0 rows)

According to the strict rule of SQL, one instance of NULL does _not_ equal
another instance of NULL when considered in a where clause. So "where txt =
NULL" should never show you anything. To find a NULL value, use "where txt
IS NULL".

However, in the past, PG has allowed "where txt = NULL" (some other
databases do, too). In recent versions, PG only does this if you set the
option "transform_null_equals" in postgresql.conf or using SET. This option
is required by some stupid clients, such as Microsoft Access.

HTH.

Joel BURTON | joel(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant