Thread: How to select rows with values set to NULL

Hello,

InfluenzaWeb=# create table test (
InfluenzaWeb(#   id int,
InfluenzaWeb(#   txt varchar(17) default NULL);
CREATE
InfluenzaWeb=# insert into test values( 1 );
INSERT 553756 1
InfluenzaWeb=# select * from test;
 id | txt
----+-----
  1 |
(1 row)

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

InfluenzaWeb=#  insert into test values(2,'text');
INSERT 553757 1
InfluenzaWeb=# select * from test where txt like '%';
 id | txt
----+------
  2 | text
(1 row)

InfluenzaWeb=# select * from test where not txt like '%';
 id | txt
----+-----
(0 rows)

So how to get all rows which txt has the value NULL?

Kind regards

         Andreas.

On Thu, May 23, 2002 at 10:00:05AM +0200, Tille, Andreas wrote:
> InfluenzaWeb=# select * from test where txt = NULL;
>  id | txt
> ----+-----
> (0 rows)

Try:

select * from test where txt is NULL;

HTH,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Canada, Mexico, and Australia form the Axis of Nations That
> Are Actually Quite Nice But Secretly Have Nasty Thoughts About America

On Thu, 23 May 2002, Tille, Andreas wrote:

Seems to work here:

jaa=# create table test2(id int, txt varchar(17) default NULL);
CREATE

jaa=# insert into  test2 values(1);
INSERT 420453 1

jaa=# select * from test2 where txt = null;
 id | txt
----+-----
  1 |
(1 row)

psql (PostgreSQL) 7.1.2


BR, Jani

>
> Hello,
>
> InfluenzaWeb=# create table test (
> InfluenzaWeb(#   id int,
> InfluenzaWeb(#   txt varchar(17) default NULL);
> CREATE
> InfluenzaWeb=# insert into test values( 1 );
> INSERT 553756 1
> InfluenzaWeb=# select * from test;
>  id | txt
> ----+-----
>   1 |
> (1 row)
>
> InfluenzaWeb=# select * from test where txt = NULL;
>  id | txt
> ----+-----
> (0 rows)
>
> InfluenzaWeb=#  insert into test values(2,'text');
> INSERT 553757 1
> InfluenzaWeb=# select * from test where txt like '%';
>  id | txt
> ----+------
>   2 | text
> (1 row)
>
> InfluenzaWeb=# select * from test where not txt like '%';
>  id | txt
> ----+-----
> (0 rows)
>
> So how to get all rows which txt has the value NULL?
>
> Kind regards
>
>          Andreas.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--
Jani Averbach

> -----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

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.