DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=H/CBCfDFT4/xRWwKFXcB7+ZAWhTADAZP9HQLsk00PkfOg2pLBiNcigwDFakjfpbX7O
	h2ULx2WCbKKudod18XST0/fKkMXJjk7tboYS7RrV7ScuPnJ9HxunMV3e4+umkC6pSOZJ
	dFD291eLyqtI2EfmZwom0mRuL7p3YUjOWAE1o=
MIME-Version: 1.0
In-Reply-To: <162867791001130536w4a14520bobc67d1c7e7e47862@mail.gmail.com>
References: <65d71e1c1001130102j5f46299cm87477efcfd0c5a51@mail.gmail.com>
	<162867791001130536w4a14520bobc67d1c7e7e47862@mail.gmail.com>
Date: Wed, 13 Jan 2010 14:55:40 +0100
Message-ID: <162867791001130555s42c369dif0b5ccd668f08923@mail.gmail.com>
Subject: Re: Substring auto trim
From: Pavel Stehule <pavel.stehule@gmail.com>
To: "Charles O'Farrell" <charleso@gmail.com>
Cc: pgsql-bugs@postgresql.org
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

2010/1/13 Pavel Stehule <pavel.stehule@gmail.com>:
> Hello
>
> 2010/1/13 Charles O'Farrell <charleso@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 trim=
med
>> automatically. This causes incorrect results when comparing to a value t=
hat
>> may contain trailing spaces.
>>
>> select * from bar where substr(foo, 1, 4) =3D 'AB=C2=A0 '
>>
>
> 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=3D# create table f(a character(5));
CREATE TABLE
postgres=3D# insert into f values('a'),('ab'),('abc');
INSERT 0 3
postgres=3D# select * from f;
   a
-------
 a
 ab
 abc
(3 rows)

postgres=3D# select * from f where substr(a,1,3) =3D 'a  ';
   a
-------
 a
(1 row)

postgres=3D# select * from f where substr(a,1,3) =3D '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 (Ubu=
ntu
>> 4.4.1-4ubuntu8) 4.4.1, 32-bit
>>
>