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
