Re: New thoughts about indexing cross-type comparisons

Tom Lane wrote:
>
> Dave Smith <dave(dot)smith(at)candata(dot)com> writes:
> > If this is only dealing with constants, why not just explicitly add a
> > cast to the constant of the column type at the planner level. It would
> > solve this problem as well ...
>
> > create table test (f int2);
> > select * from test where f=cast('1981928928921' as int2);
> > ERROR: pg_atoi: error reading "1981928928921": Numerical result out of
> > range
>
> > select * from test where f=1981928928921;
> > f
> > ---
> > (0 rows)
>
> Uh, your own example points out why we can't just insert a cast to int2
> --- it changes the results. Or am I missing your point?

Because PG doesn't have the functionality to catch exceptions
until now, we can't call cast functions tentatively. But for
example, why should the pg_atoi error cause an unrecoverable
error immediately ? IMHO we should be able to call cast functions
internally without an anxiety to cause an unrecoverable error.

regards,
Hiroshi Inoue
http://www.geocities.jp/inocchichichi/psqlodbc/