2010/1/13 Charles O'Farrell <charleso(at)gmail(dot)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
> 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.
Function substr has first parameter of type "text". When pg call this
function, then it does conversion from char(x) to text.
> 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
In response to
pgsql-bugs by date
|Next:||From: Pavel Stehule||Date: 2010-01-13 13:55:40|
|Subject: Re: Substring auto trim|
|Previous:||From: Charles O'Farrell||Date: 2010-01-13 09:02:31|
|Subject: Substring auto trim|