Thread: Null in the where-clause

Hello everybody,

I've got a problem with the "null"-statement within the where-clause.
I installed the postgres-server 7.2.1 on a Suse Linux machine.
Then I imported an existing databasedump.
But when I make the following query:
select * from tbl_categories where categoryid=null;
I get 0 rows as result, but there are several records, which
corrsponds to my query.
What could be the problem? Do I have to reinstall the server
with a special parameter?

Thanx in advance
Michael Walzl

No you do not have to re-install the server.

When using NULL you may not use the = sign because a null is an undefined
piece of data meaning it may exist but you don't know the value or it may
not exist or you just don't know what is meant to go into that field.

Instead of equal use the IS command and say

WHERE field IS NULL

This will produce the result you are looking for.

HTH

Darren

On 5 Jul 2002, Michael Walzl wrote:

> Hello everybody,
>
> I've got a problem with the "null"-statement within the where-clause.
> I installed the postgres-server 7.2.1 on a Suse Linux machine.
> Then I imported an existing databasedump.
> But when I make the following query:
> select * from tbl_categories where categoryid=null;
> I get 0 rows as result, but there are several records, which
> corrsponds to my query.
> What could be the problem? Do I have to reinstall the server
> with a special parameter?
>
> Thanx in advance
> Michael Walzl
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>

--
Darren Ferguson

On 5 Jul 2002, Michael Walzl wrote:

> Hello everybody,
>
> I've got a problem with the "null"-statement within the where-clause.
> I installed the postgres-server 7.2.1 on a Suse Linux machine.
> Then I imported an existing databasedump.
> But when I make the following query:
> select * from tbl_categories where categoryid=null;
> I get 0 rows as result, but there are several records, which
> corrsponds to my query.

I doubt that ;)  categoryid=null never returns true, even when
categoryid is null itself (as per SQL spec).  You really want the
query:
select * from tbl_categories where categoryid IS null;

or, set TRANSFORM_NULL_EQUALS which turns =null into IS null,
but that's not recommended unless you can't change the queries
since that might break if someone expects =null to work properly.

Michael Walzl dijo:

> I've got a problem with the "null"-statement within the where-clause.
> I installed the postgres-server 7.2.1 on a Suse Linux machine.
> Then I imported an existing databasedump.
> But when I make the following query:
> select * from tbl_categories where categoryid=null;
> I get 0 rows as result, but there are several records, which
> corrsponds to my query.

No, there is none.  NULL never equals anything, not even null itself.

Try
select * from tbl_categories where categoryid is null;

--
Alvaro Herrera (<alvherre[a]atentus.com>)
"Ninguna manada de bestias tiene una voz tan horrible como la humana" (Orual)

Michael Walzl sez:
} Hello everybody,
}
} I've got a problem with the "null"-statement within the where-clause.
} I installed the postgres-server 7.2.1 on a Suse Linux machine.
} Then I imported an existing databasedump.
} But when I make the following query:
} select * from tbl_categories where categoryid=null;
} I get 0 rows as result, but there are several records, which
} corrsponds to my query.
} What could be the problem? Do I have to reinstall the server
} with a special parameter?

The problem is that null isn't equal to anything, including itself. While a
null value in a column is not equal to null, it IS NULL:

select * from tbl_categories where categoryid is null;

} Thanx in advance
} Michael Walzl
--Greg