Update to 9.0.6 - problems with trim and substring

[Sauer Annegret]

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 installed a test database similar to our real one. On this test database I have been testing all our SQL scripts we are running regularly 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. With the 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ückliche Typumwandlungen 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ückliche Typumwandlungen 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"?

Best regards,
Annegret Sauer


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

Schloss Westhof-Ost | 70599 Stuttgart
Tel.: [phone] | Fax: [phone]
Email: ***@verwaltung.uni-hohenheim.de
https://kim.uni-hohenheim.de

[Andreas Kretschmer]

Post by Sauer Annegret
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 installed a test database similar to our real one. On this test database I have been testing all our SQL scripts we are running regularly 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. With the 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?

Post by Sauer Annegret
"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ückliche Typumwandlungen 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ückliche Typumwandlungen hinzufügen.
Zeichen: 289"
"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°