Odd behavior with NULL value

From: bsmith(at)h-e(dot)com (Bob Smith, Hammett & Edison, Inc(dot))
To: pgsql-admin(at)postgresql(dot)org
Subject: Odd behavior with NULL value
Date: 2001-12-21 00:24:33
Message-ID: 000001411043091739073@h-e.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

See example below of using NULL values with type DATE. It behaves
strangely in expressions, "(x <> null)" gives an entirely different
result than "not(x = null)". Is this intended behavior, if so, why?
If not, is this a bug?

On a related note, does anyone know if 'current' works with DATE? With
TIMESTAMP it always evaluates to the time as of the retrieval of the
value, not as of the insertion, but for DATE it looks like it gets
evaluated at insertion (I can't tell for sure from my test db for another
8 hours or so, I could just roll the date forward on the server, but I'd
like to keep my job...)

Thanks to anyone who can shed some light on this!

rsj=> \d test
Table "test"
Attribute | Type | Modifier
-----------+---------+----------
key | integer |
date | date |

rsj=> select * from test;
key | date
-----+------------
1 | 2001-12-20
2 | 2001-12-20
3 |
(3 rows)

rsj=> select * from test where date = null;
key | date
-----+------
3 |
(1 row)

rsj=> select * from test where date <> null;
key | date
-----+------
(0 rows)

rsj=> select * from test where not(date = null);
key | date
-----+------------
1 | 2001-12-20
2 | 2001-12-20
(2 rows)

rsj=>

|\ _,,,---,,_ Bob Smith
/,`.-'`' -. ;-;;,_ Hammett & Edison, Inc.
|,4- ) )-,_. ,\ ( `'-' bsmith(at)h-e(dot)com
'---''(_/--' `-'\_)

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Bruno Wolff III 2001-12-21 01:09:35 Re: Odd behavior with NULL value
Previous Message Christian 2001-12-20 19:14:52 Re: Data partitioning