Re: cast problem in Postgresql 9.0.1

On Monday, January 31, 2011 10:14:29 pm AI Rumman wrote:
> I migrated my DB from Postgresql 8.1 to Postgresql 9.0.1.
>
> I have a table "testtab"
> \d testtab
> id int,
> hours varchar
>
> When I execute the following:
> select sum(hours) from testtab
> I get cast error.

Try:
select sum(hours::int) from testtab;

>
> Then,
>
> I created following IMPLICIT CAST functions in my DB =>
>
> CREATE FUNCTION pg_catalog.integer(varchar) RETURNS int4 STRICT IMMUTABLE
> LANGUAGE SQL AS 'SELECT int4in(varcharout($1));';
> CREATE CAST (varchar AS integer) WITH FUNCTION pg_catalog.integer(varchar)
> AS IMPLICIT;
>
> CREATE FUNCTION pg_catalog.smallint(varchar) RETURNS smallint STRICT
> IMMUTABLE LANGUAGE SQL AS 'SELECT int2in(varcharout($1));';
> CREATE CAST (varchar AS smallint) WITH FUNCTION
> pg_catalog.smallint(varchar) AS IMPLICIT;
>
> Now, the above query works, but
> SELECT COALESCE(hours,0) from testtab
> failed.
>
> Any idea why?

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com