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: 162867791001130536w4a14520bobc67d1c7e7e47862@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
Hello

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.

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
>

In response to

Responses

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-2014 The PostgreSQL Global Development Group