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.klaver@xxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
