Re: Substring auto trim

"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