2010/1/13 Pavel Stehule <pavel.steh...@gmail.com>: > Hello > > 2010/1/13 Charles O'Farrell <charl...@gmail.com>: >> Hi guys, >> >> I'm not sure whether this a really dumb question, but I'm curious as to what >> might be the problem. >> >> We have a column 'foo' which is of type character (not varying). >> >> select substr(foo, 1, 10) from bar >> >> The result of this query are values whose trailing spaces have been trimmed >> automatically. This causes incorrect results when comparing to a value that >> may contain trailing spaces. >> >> select * from bar where substr(foo, 1, 4) = 'ABĀ ' >> > > You have to write C function substr for type "any" :( Because "char" > and char(n) are two different types, and you cannot to write function > for char(n) > > >> I should mention that we normally run Oracle and DB2 (and have done for many >> years), but I have been pushing for Postgres as an alternative. >> Fortunately this is all handled through Hibernate, and so for now I have >> wrapped the substr command in rpad which seems to do the trick. >> >> Any light you can shed on this issue would be much appreciated. >>
I thing, so there is workaround, create or replace function substr(character, int, int) returns character as $$ select substr($1::cstring::text,$2,$3) $$ language sql; postgres=# create table f(a character(5)); CREATE TABLE postgres=# insert into f values('a'),('ab'),('abc'); INSERT 0 3 postgres=# select * from f; a ------- a ab abc (3 rows) postgres=# select * from f where substr(a,1,3) = 'a '; a ------- a (1 row) postgres=# select * from f where substr(a,1,3) = 'ab '; a ------- ab (1 row) Regards Pavel Stehule > > Function substr has first parameter of type "text". When pg call this > function, then it does conversion from char(x) to text. > > Regards > Pavel Stehule > > >> Cheers, >> >> Charles O'Farrell >> >> PostgreSQL 8.4.2 on i486-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu >> 4.4.1-4ubuntu8) 4.4.1, 32-bit >> > -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs