Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id BBE8D6328C8 for ; Wed, 13 Jan 2010 05:02:43 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 11703-02 for ; Wed, 13 Jan 2010 09:02:33 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from ey-out-2122.google.com (ey-out-2122.google.com [74.125.78.26]) by mail.postgresql.org (Postfix) with ESMTP id E2EF5632511 for ; Wed, 13 Jan 2010 05:02:32 -0400 (AST) Received: by ey-out-2122.google.com with SMTP id 9so63972eyd.3 for ; Wed, 13 Jan 2010 01:02:31 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:date:message-id:subject :from:to:content-type; bh=CdP/sHSGd9m2pZOLMl/rBw3Iv5mi4qM/orvEa3yutJw=; b=OCsKLb8quWQrMlofqMmndU3tB+/OEqZwL9bICdQR/YRadbdPxPvRJDQ4gMFUS1YcQ+ avhFKrh/eh83DSNhME18GtXg18v3c68jkF75dMpnktYoarmv8SNy7YMJMcWpwuyOlnDf aP6rQImKdnk2tvJsH2oLD03ZJT/tl0VE0vylI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:date:message-id:subject:from:to:content-type; b=Ce9mnqElp0dvjhucIM4VMDK0g0dd2nnrUKsxUGJsa68Ft2JVArFs2eAsR8VeXBobUB 6gtPOVHTa6unLU9pyEEImf0sJCqMkutnVoetEgUwL9SmprfjUDo0kaHSHTUz/aooYi1E ZpA6LMvoj+KtfAwRPJsN8mqUb6HySqS7BkQV0= MIME-Version: 1.0 Received: by 10.216.88.15 with SMTP id z15mr1284224wee.113.1263373351055; Wed, 13 Jan 2010 01:02:31 -0800 (PST) Date: Wed, 13 Jan 2010 19:02:31 +1000 Message-ID: <65d71e1c1001130102j5f46299cm87477efcfd0c5a51@mail.gmail.com> Subject: Substring auto trim From: "Charles O'Farrell" To: pgsql-bugs@postgresql.org Content-Type: multipart/alternative; boundary=0016e6d97735cef3b3047d08076f X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.59 tagged_above=-10 required=5 tests=BAYES_00=-2.599, FH_DATE_PAST_20XX=3.188, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201001/108 X-Sequence-Number: 25612 --0016e6d97735cef3b3047d08076f Content-Type: text/plain; charset=ISO-8859-1 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 ' 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. 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 --0016e6d97735cef3b3047d08076f Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable 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 = 9;foo' which is of type character (not varying).

select substr(f= oo, 1, 10) from bar

The result of this query are values whose trailing spaces have been tri= mmed automatically. This causes incorrect results when comparing to a value= that may contain trailing spaces.

select * from bar where substr(foo, 1, 4) =3D 'AB=A0 '

I sho= uld mention that we normally run Oracle and DB2 (and have done for many yea= rs), 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 s= ubstr command in rpad which seems to do the trick.

Any light you can shed on this issue would be much appreciated.

= Cheers,

Charles O'Farrell

PostgreSQL 8.4.2 on i486-pc-lin= ux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 4.4.1-4ubuntu8) 4.4.1, 32-bit<= br> --0016e6d97735cef3b3047d08076f--