Postgres Pro
Downloads
Home   >   mailing lists

Thread: behaviour of LENGTH() in postgresql v. 8

behaviour of LENGTH() in postgresql v. 8

From
"Marcus Andree S. Magalhaes"
Date:
Dear all,

While checking the last Beta3-Beta4 Changelog of Postgres 8.0,
I saw the following:

    * doc/TODO: Remove completed items:
        <snip>
          * -Make LENGTH() of
      CHAR() not count trailing spaces

If I understand correctly, this will lead to the following situation:

      CHAR_LENGTH('ABCD') == CHAR_LENGTH('ABCD    ') == 4

This will definitely break some of our applications.

Just want to be sure if this LENGTH() behaviour _will_ appear
on the next version of Postgres. If true, can we somehow revert
to the current LENGTH() behaviour?

Can someone, please, clarify this issue?

Thanks a lot.

Re: behaviour of LENGTH() in postgresql v. 8

From
Bruce Momjian
Date:
Marcus Andree S. Magalhaes wrote:
>
> Dear all,
>
> While checking the last Beta3-Beta4 Changelog of Postgres 8.0,
> I saw the following:
>
>     * doc/TODO: Remove completed items:
>         <snip>
>           * -Make LENGTH() of
>       CHAR() not count trailing spaces
>
> If I understand correctly, this will lead to the following situation:
>
>       CHAR_LENGTH('ABCD') == CHAR_LENGTH('ABCD    ') == 4
>
> This will definitely break some of our applications.
>
> Just want to be sure if this LENGTH() behaviour _will_ appear
> on the next version of Postgres. If true, can we somehow revert
> to the current LENGTH() behaviour?
>
> Can someone, please, clarify this issue?

You should review the release notes.  The full description is:

     * Make length() disregard trailing spaces in CHAR(n) (Gavin)
       This change was made to improve consistency: trailing spaces
       are semantically insignificant in CHAR(n) data, so
       they should not be counted by length().

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: behaviour of LENGTH() in postgresql v. 8

From
Tom Lane
Date:
"Marcus Andree S. Magalhaes" <marcus.magalhaes@vlinfo.com.br> writes:
> If I understand correctly, this will lead to the following situation:
>       CHAR_LENGTH('ABCD') == CHAR_LENGTH('ABCD    ') == 4
> This will definitely break some of our applications.

If you think trailing spaces are semantically significant, you should be
using varchar or text fields to store them.  In CHAR(n) fields they are
not significant, and we're doing our best to make that interpretation
consistent across all operations.

Just for the record, I get this with CVS tip:

regression=# select CHAR_LENGTH('ABCD    ');
 char_length
-------------
           8
(1 row)

regression=# select CHAR_LENGTH('ABCD    '::text);
 char_length
-------------
           8
(1 row)

regression=# select CHAR_LENGTH('ABCD    '::varchar);
 char_length
-------------
           8
(1 row)

regression=# select CHAR_LENGTH('ABCD    '::char(8));
 char_length
-------------
           4
(1 row)

(the first and second cases are in fact the same)

            regards, tom lane

Re: behaviour of LENGTH() in postgresql v. 8

From
"Marcus Andree S. Magalhaes"
Date:

Thanks for your answer. We're already using text and varchar, so we
should be safe when we migrate our server.

<snip>
> If you think trailing spaces are semantically significant, you should be
> using varchar or text fields to store them.  In CHAR(n) fields they are
> not significant, and we're doing our best to make that interpretation
> consistent across all operations.
<snip>