Обсуждение: Data type conversion again

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

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

Ooooh. Some motivation :) (sorry, couldn't resist...)

> 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.
> test=> select dtm::DATE from dtm_test;
> ERROR:  Unable to convert null datetime to date

I'm suprised at this message and problem; will try to address it for
v6.4. In testing on my alpha code I'm not handling this any better yet
(and in fact have new problems with the null constant).

> to say 'Because of the range problems', then tell me what
> 'infinity'::TIMESTAMP is for.

A kludge to work around the limited range. Although you kids may not
realize it, "-infinity" is actually substantially earlier than 1902,
despite Postgres' behavior :)

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

Cataloging specific problems as you are doing is helpful. If you want to
keep a running list then I can ask you for it later, once I've gotten
the new type conversion code off the ground.

We've been chipping away at the type conversion and casting problem for
the last several releases, and things are a _lot_ more solid than they
were when we started. Consolidating types for v6.4 as we are doing with
character strings and perhaps date/time types will also help us focus on
the useful ones which remain.

                     - Tom