Thread: Bug Repoprt- Casting Issues
============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================
Your name : Dirk Elmendorf
Your email address : delmendo@rackspace.com
System Configuration
---------------------
Architecture (example: Intel Pentium) : Intel Pentium
Operating System (example: Linux 2.0.26 ELF) :RedHat Linux 6.1/ 2.2.12-20
PostgreSQL version (example: PostgreSQL-6.5.1): PostgreSQL-7.0
Compiler used (example: gcc 2.8.0) : egcs-2.9.66
Please enter a FULL description of your problem:
------------------------------------------------
Casting system was changed.
Cannot cast and int4 to a date using the :: or cast as syntax to cast
an int4 to a date.
Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------
create table test
(
seconds int4
);
insert into test (seconds) values(951414912);
select seconds::date from test;
ERROR: Cannot casty type 'int4' to 'date'
select date(seconds) from test;
date
--------
200-02-24
If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------
--
______________________________________________________________________
Dirk Elmendorf, CTE Main: 210-892-4000
Rackspace,Ltd. Direct: 210-892-4005
Weston Center Fax: 210-892-4329
112 East Pecan, Suite 600 Email:delmendo@rackspace.com
San Antonio, TX 78205 <http://www.rackspace.com>
Dirk Elmendorf <delmendo@rackspace.com> writes:
> insert into test (seconds) values(951414912);
> select seconds::date from test;
> ERROR: Cannot casty type 'int4' to 'date'
> select date(seconds) from test;
> date
> --------
> 200-02-24
I don't think this is a bug, actually. The foo::type notation is
by design stricter than type(foo) --- the :: form will only succeed
when there is a *direct* one-step conversion between the source and
target types, whereas type(foo) will succeed if the parser can find
any way of converting foo to something that any available type()
converter will take.
In this example there is no int4-to-date function, and the path you
are actually taking is
regression=# select 951414912::int4::abstime::date;
?column?
------------
2000-02-24
(1 row)
If you'd prefer not to be concerned about the intermediate conversion to
abstime, then use the date() notation. The :: notation is designed
for controlling the type conversion exactly.
regards, tom lane
Tom Lane writes: > If you'd prefer not to be concerned about the intermediate conversion > to abstime, then use the date() notation. The :: notation is designed > for controlling the type conversion exactly. Wow, that is definitely confusing. I had always thought that `::' is "cast, no matter how", and date() is "call the function date, which happens to do the conversion". But now it seems that the supposed "cast" syntax is really just a dumb function call whereas the function syntax actually does more intelligent work behind the scenes. I have a feeling these type conversion issues aren't going away for a long time... -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
Peter Eisentraut <peter_e@gmx.net> writes:
> Tom Lane writes:
>> If you'd prefer not to be concerned about the intermediate conversion
>> to abstime, then use the date() notation. The :: notation is designed
>> for controlling the type conversion exactly.
> Wow, that is definitely confusing. I had always thought that `::' is
> "cast, no matter how", and date() is "call the function date, which
> happens to do the conversion". But now it seems that the supposed "cast"
> syntax is really just a dumb function call whereas the function syntax
> actually does more intelligent work behind the scenes.
Actually, most of the "intelligence" falls out of the fact that the
system is willing to do type-coercions to make the inputs to a function
call match the requirements of the called function. So when you write
date(int4var) you are actually getting date(int4var::abstime).
While it may seem confusing, I don't see a better alternative. The
usability of the system would be greatly reduced if we didn't perform
implicit type conversion of function inputs (unless we created a much
larger number of functions than we have now). OTOH, I don't think that
foo::bar should be willing to detour silently through several other
types to make the conversion from foo to bar...
> I have a feeling these type conversion issues aren't going away for a long
> time...
We could do with some better documentation on the subject, at least.
regards, tom lane