agora inbox for pgsql-bugs@postgresql.org  

help / color / mirror / Atom feed

From: Charles O'Farrell <charleso@gmail.com>
To: pgsql-bugs@postgresql.org
Subject: Substring auto trim
Date: Wed, 13 Jan 2010 19:02:31 +1000
Message-ID: <65d71e1c1001130102j5f46299cm87477efcfd0c5a51@mail.gmail.com> (raw)

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

view thread (10+ messages)  latest in thread

Message-ID: <65d71e1c1001130102j5f46299cm87477efcfd0c5a51@mail.gmail.com>
Permalink:  ../65d71e1c1001130102j5f46299cm87477efcfd0c5a51@mail.gmail.com/
Also on:    postgresql.org/message-id/65d71e1c1001130102j5f46299cm87477efcfd0c5a51@mail.gmail.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: charleso@gmail.com
  Subject: Re: Substring auto trim
  In-Reply-To: <65d71e1c1001130102j5f46299cm87477efcfd0c5a51@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox