Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id CDFD0633C75 for ; Wed, 13 Jan 2010 09:37:36 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 78215-01 for ; Wed, 13 Jan 2010 13:37:15 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-ew0-f213.google.com (mail-ew0-f213.google.com [209.85.219.213]) by mail.postgresql.org (Postfix) with ESMTP id 0BDD063381E for ; Wed, 13 Jan 2010 09:36:24 -0400 (AST) Received: by ewy5 with SMTP id 5so13478995ewy.34 for ; Wed, 13 Jan 2010 05:36:19 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type :content-transfer-encoding; bh=azotAynY7HuanXNdA9S90nWlh7sp48cr3JuMu324sSg=; b=CjZNmNUzAx07XaSVbMtKHl4RKBrKDr2Fz+3RtSVhERO4WHvxEHlMBaLh+RhE1ShSLl AWF0cjHzMhcsn3mGXeN0xOindfPdhPXOVkI/XAFPHcv9sGTku1m5XijsFL0Vul4A8sb1 X+aRsXZog42btJ5h1WDio0ijEwEU+b91Iw1h4= 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=TAgVAUy999bbFqKZpqEBP3w3dO+pJrmwaa2Pwj9GUbreFPk4D60PVetoMQoi+lHA1W Ymj/5OXnQxe96FB5OlV+7q1qJykHk1CurnwrGCvROpE12CfRJACjomQT2BZb6m0vOfrP XoJHX4CMZo13FdH3QYmBdjoPBbH9W6xZ8VdoU= MIME-Version: 1.0 Received: by 10.216.89.8 with SMTP id b8mr132849wef.180.1263389779307; Wed, 13 Jan 2010 05:36:19 -0800 (PST) In-Reply-To: <65d71e1c1001130102j5f46299cm87477efcfd0c5a51@mail.gmail.com> References: <65d71e1c1001130102j5f46299cm87477efcfd0c5a51@mail.gmail.com> Date: Wed, 13 Jan 2010 14:36:19 +0100 Message-ID: <162867791001130536w4a14520bobc67d1c7e7e47862@mail.gmail.com> Subject: Re: Substring auto trim From: Pavel Stehule To: "Charles O'Farrell" Cc: pgsql-bugs@postgresql.org Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.523 tagged_above=-10 required=5 tests=AWL=0.076, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 201001/109 X-Sequence-Number: 25613 Hello 2010/1/13 Charles O'Farrell : > Hi guys, > > I'm not sure whether this a really dumb question, but I'm curious as to w= hat > 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 trimm= ed > automatically. This causes incorrect results when comparing to a value th= at > 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 m= any > 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 (Ubun= tu > 4.4.1-4ubuntu8) 4.4.1, 32-bit >