agora inbox for pgsql-bugs@postgresql.org  

help / color / mirror / Atom feed

From: Tom Lane <tgl@sss.pgh.pa.us>
To: Charles O'Farrell <charleso@gmail.com>
Cc: pgsql-bugs@postgresql.org
Subject: Re: Substring auto trim
Date: Wed, 13 Jan 2010 10:35:35 -0500
Message-ID: <10878.1263396935@sss.pgh.pa.us> (raw)
In-Reply-To: <65d71e1c1001130102j5f46299cm87477efcfd0c5a51@mail.gmail.com>
References: <65d71e1c1001130102j5f46299cm87477efcfd0c5a51@mail.gmail.com>

"Charles O'Farrell" <charleso@gmail.com> writes:
> 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.

What's the data type of the value being compared to?  I get, for instance,

postgres=# select substr('ab  '::char(4), 1, 4) = 'ab  '::char(4);
 ?column? 
----------
 t
(1 row)

The actual value coming out of the substr() is indeed just 'ab',
but that ought to be considered equal to 'ab  ' anyway in char(n)
semantics.

Postgres considers that trailing blanks in a char(n) value are
semantically insignificant, so it strips them when converting to a type
where they would be significant (ie, text or varchar).  What's happening
in this scenario is that substr() is defined to take and return text,
so the stripping happens before substr ever sees it.

As Pavel noted, you could possibly work around this particular case by
defining a variant of substr() that takes and returns char(n), but on
the whole I'd strongly advise switching over to varchar/text if
possible.  The semantics of char(n) are so weird/braindamaged that
it's best avoided.

BTW, if you do want to use the workaround, this seems sufficient:

create function substr(char,int,int) returns char
  strict immutable language internal as 'text_substr' ; 

It's the same C code, you're just avoiding the coercion on input.

			regards, tom lane

view thread (10+ messages)  latest in thread

Message-ID: <10878.1263396935@sss.pgh.pa.us>
Permalink:  ../10878.1263396935@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/10878.1263396935@sss.pgh.pa.us

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: tgl@sss.pgh.pa.us, charleso@gmail.com
  Subject: Re: Substring auto trim
  In-Reply-To: <10878.1263396935@sss.pgh.pa.us>

* 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