Thread: Update to 9.0.6 - problems with trim and substring

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.  

Example of such a script:

Select
trim(both from sos.name ) as name,
trim(both from sos.mtknr) as immatriculationnumber
trim(both from sos.pokfz ) as country,
trim(both from sos.semester ) as sem
[...]
from sos, sossys
where ...

CREATE TABLE sos
(name character(35),
pokfz character(3),
mtknr integer,
semester smallint,
...)
WITH (OIDS=TRUE);

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?
Iam 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"? 

Best regards,
Annegret Sauer


---
Annegret Sauer
Universität Hohenheim
Kommunikations-, Informations- und Medienzentrum (630)
IT-Dienste | Verwaltungssysteme

Schloss Westhof-Ost | 70599 Stuttgart
Tel.:  +49 711 459-23381 | Fax: +49 711 459-24224
Email: sauer@verwaltung.uni-hohenheim.de
https://kim.uni-hohenheim.de

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°