Re: Update to 9.0.6 - problems with trim and substring

Sauer Annegret <sauer@Verwaltung.uni-hohenheim.de> wrote:

> Hello!
>
> We want to move our database from Postgres 8.4 to Postgres 9.0.6. As a first step we installed Postgres 9.0.6 and
installeda test database similar to our real one. On this test database I have been testing all our SQL scripts we are
runningregularly via cronjobs. Most of the scripts work fine. However, some of the scripts use the functions "trim" and
"substring"in combination with columns using data types other than character or varchar - and are not working anymore.
Withthe old version of Postgres it was no problem.  

wild guess: the old version was 8.2, not 8.4, right?

And why 9.0 as new version?


> The error message (in German) is as follows:
>
> "FEHLER: Funktion pg_catalog.btrim(smallint) existiert nicht
> LINE 11: trim(both from sos.semester) as sem
>
> HINT: Keine Funktion stimmt mit dem angegebenen Namen und den Argumenttypen überein. Sie müssen möglicherweise
ausdrücklicheTypumwandlungen hinzufügen.  
>
> ***** Fehler ****
> FEHLER: Funktion pg_catalog.btrim(smallint) existiert nicht
> SQL Status:42883
> HINT: Keine Funktion stimmt mit dem angegebenen Namen und den Argumenttypen überein. Sie müssen möglicherweise
ausdrücklicheTypumwandlungen hinzufügen.  
> Zeichen: 289"
>
> (English version might be like this:
> "ERROR: function pg_catalog.btrim(smallint) does not exist
> SQL state: 42883
> Hint: No function matches the given name and argument types. You may need to add explicit type casts."
>
> Do I have to change the data type of every column if I want to use "trim" and "substring" (and if so, how do I do
this?I am a real beginner...) or is there another way of getting this to work? Or is there an alternative to "trim" or
"substring"that works with "smallint" or "integer"? 

simple example:

test=*# select btrim(5);
ERROR:  function btrim(integer) does not exist
LINE 1: select btrim(5);
               ^
HINT:  No function matches the given name and argument types. You might
need to add explicit type casts.
Time: 28,559 ms
test=*# select btrim(5::text);
 btrim
-------
 5
(1 row)


other solution (but NOT recommended!):
http://petereisentraut.blogspot.de/2008/03/readding-implicit-casts-in-postgresql.html

(not recommended because of it's unclean to work with wrong data types)


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°