cast problem in Postgresql 9.0.1

From: AI Rumman <rummandba(at)gmail(dot)com>
To: pgsql-general General <pgsql-general(at)postgresql(dot)org>
Subject: cast problem in Postgresql 9.0.1
Date: 2011-02-01 06:14:29
Message-ID: AANLkTi=yc+NcCcy=_S42eLCn79vOZawbEJMa=Pe57gvb@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adarsh Sharma 2011-02-01 06:55:12 Serial Vs Sequence
Previous Message Joshua Tolley 2011-02-01 05:40:22 Re: Automatic database monitoring tool for PostgreSQL ... new project