OK guys this really concerns me. And I really do consider it a bug.
Now if Thomas has gotten this working for 6.4 I'll shut my trap. But
until I hear someone tell me that I'm overreacting or that it's fixed
I'll continue to try to raise support for one or the other. This is my
little experiment:
test=> create table dtm_test (dtm datetime);
CREATE
test=> insert into dtm_test VALUES (NOW()::DATETIME);
INSERT 228745 1
test=> select * from dtm_test;
dtm
----------------------------
Wed May 06 13:37:56 1998 CDT
(1 row)
test=> select dtm::DATE from dtm_test;
date
----------
05-06-1998
(1 row)
test=> INSERT INTO dtm_test VALUES (NULL);
INSERT 228746 1
test=> select * from dtm_test;
dtm
----------------------------
Wed May 06 13:37:56 1998 CDT
(2 rows)
test=> select dtm::DATE from dtm_test;
ERROR: Unable to convert null datetime to date
I do realize that NULL signifies a undefined answer, but wouldn't it be
a good idea to convert NULL::DATETIME to NULL::DATE, NULL::TIME, and
NULL::TIMESTAMP.
only DATETIME, TIMESTAMP, DATE, and TIME are being considered here but
we have other conversion holes that need to be plugged.
We currently have w/o NULL conversion:
TIMESTAMP -> DATETIME
Why not have TIME and DATE as well?
DATETIME -> DATE, TIME
Why not have TIMESTAMP if were going to have it? And if your going
to say 'Because of the range problems', then tell me what
'infinity'::TIMESTAMP is for.
DATE -> DATETIME
Once more I ask why not TIMESTAMP?
TIME -> nothing that I could find.
What this means is that for a lot of conversions you'd have to do at
least two CAST steps. Some conversions (even though they would make
since) aren't possible.
There are similar problems with varchar, text, and char.
I'm not even sure about other types.
If I'm way off base here I'm sure you guys will let me know. If not,
what can I do to help fix this.
Waiting patiently,
-DEJ