Skip site navigation (1) Skip section navigation (2)

Re: Substring auto trim

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Charles O'Farrell" <charleso(at)gmail(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Substring auto trim
Date: 2010-01-13 15:35:35
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs
"Charles O'Farrell" <charleso(at)gmail(dot)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);
(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)

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

In response to


pgsql-bugs by date

Next:From: Kevin GrittnerDate: 2010-01-13 16:01:51
Subject: Re: Substring auto trim
Previous:From: Pavel StehuleDate: 2010-01-13 13:55:40
Subject: Re: Substring auto trim

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group