Skip site navigation (1) Skip section navigation (2)

Re: Substring auto trim

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: "Charles O'Farrell" <charleso(at)gmail(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Substring auto trim
Date: 2010-01-13 13:36:19
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs

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
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.

Function substr has first parameter of type "text". When pg call this
function, then it does conversion from char(x) to text.

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

In response to


pgsql-bugs by date

Next:From: Pavel StehuleDate: 2010-01-13 13:55:40
Subject: Re: Substring auto trim
Previous:From: Charles O'FarrellDate: 2010-01-13 09:02:31
Subject: Substring auto trim

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group