DATE and NULL

[DATENULL]

Hi,

Simplifying my problem its this,

I have inserted a record in to a 'table1' which has fields 'name' (CHAR(10), Primary Key) and 'date1' (DATE, Null)

The insert statement was

INSERT INTO table1 VALUES ('bill', null);

This shows, as you'd expect

name | date1
------------------------------
bill |

MY PROBLEM:

The following select statement returns nothing

SELECT name FROM table1 WHERE date1 = null;

Shouldn't I get a result? date1 has value null.

How can SELECT this record from a null date??

Thanks

LasLo

 

[manjeman]

u can try this..

SELECT name FROM table1 WHERE date1 = '';

 

[swampBoogie]

SELECT name FROM table1 WHERE date1 is null;

 

[rycamor]

Yes, you have to understand the meaning of NULL, and the fact that PostgreSQL treats nulls properly. Oracle unfortunately, treats nulls the same as an empty string, as do a few other commercial DBMSs, I believe, encouraging all sorts of bad programming logic. But technically, null can not be evaluated to be equal to anything, even itself, because null means "unknown". So, you cannot test for equality, or any sort of comparison, because it "does not compute". The only thing you can test for is the fact that something is null, or that it is not. thus IS NULL and IS NOT NULL are appropriate expressions.

Here is an example of how this misunderstanding can cause problems, from the PostgreSQL mailing lists.

[tt]> Hi,
>
> I work with Oracle and to it,
> a empty quote concatenation is equal NULL.
>
> ORACLE:
> select 1 from dual where '' is null
> Result: 1
> select 1 from dual where trim(' ') is null
> result: 1
> insert into tabela values ('')
> result: insert a value null in a table

Yes, Oracle is wrong here. They made a mistake a long time ago, and since
everyone started coding as though '' = NULL, they couldn't change it.
Maybe someday they will deprecate this behaviour then finally code it out
of their database, but I kinda doubt that.

> POSTGRE
> select 1 where '' is null
> Result: NULL
> select 1 where trim(' ') is null
> result: NULL
> insert into tabel values ('')
> result: insert '' in table

Yes, because NULL != NULL != '' != 0 because NULL is unknown, so in set
theory it can't be assumed to be equal to anything, even itself.

> We can't change all ours source code
> bacause there are very occurrences.

Wow. You should probably review all your code if folks were treating ''
like NULL then they've probably made some other serious mistakes in how
they treat data. I'd definitely check your data constraints, NOT NULL
stuff, things like that.
[/tt] -------------------------------------------

My PostgreSQL FAQ --

http://brainscraps.com/faq/pg_my.html

 

[newbiepg]

if I remember correctly
every null value is different
you cannot write = null in Postgresql
is null is the correct expression

there are unique indexes in postgresql which like
normal primary key indexes , unique
These however can hold null values as every null value is expected to be different from the other