Thread: cast problem in Postgresql 9.0.1

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?

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
differencesin behaviour. 

One thing to start looking at right away is whether your config parameters still make sense, in case you're re-using
yourold 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
Postgresdidn'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