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.klaver@gmail.com

On 1 Feb 2011, at 7:14, AI Rumman wrote:

I migrated my DB from Postgresql 8.1 to Postgresql 9.0.1.

That's quite a big step up. You skipped 8.2, 8.3 and 8.4 - all major releases. My advise: Test very thoroughly for more differences in behaviour.

One thing to start looking at right away is whether your config parameters still make sense, in case you're re-using your old config. Some changed names I think, and there are some new ones you might want to change.

I have a table "testtab"
\d testtab
id int,
hours varchar

That seems an odd choice for a datatype. What are you trying to accomplish by making it varchar?

And no, of course you can't sum varchars, what kind of output would you expect from that? That an older version of Postgres didn't throw an error was probably a bug.

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.

!DSPAM:737,4d48774c11731638385336!

On Tue, 2011-02-01 at 12:14 +0600, 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.

In 8.3, implicit casts were removed. You can't sum text. You need to
change the data type to a proper numerical type.

Joshua D. Drake

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

"Joshua D. Drake" <jd@commandprompt.com> writes:

On Tue, 2011-02-01 at 12:14 +0600, 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.

In 8.3, implicit casts were removed. You can't sum text.

You couldn't do it in previous releases, either.

regards, tom lane