Re: How to select rows with values set to NULL

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.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@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant