Thread: 7.4: CHAR padding inconsistency

Hello,

I read about the padding-of-CHAR-values changes in the release notes for
7.4.

Making PostgreSQL less standard compliant is sad; I also disagree
with the statement that trimming of trailing white-space is what people
expect.

What's worse, and this may be classified as an error:

create table chartest(col char(10) not null);
insert into chartest values ('AAA');

select character_length(col) from chartest;
 character_length
------------------
               10

select character_length(col || 'hey') from chartest;
 character_length
------------------
                6

SELECT CHARACTER_LENGTH(col) <
  CHARACTER_LENGTH(col||'hey') from chartest;
 ?column?
----------
 f

The last two results are horrifying, in my opinion, especially when you
consider them in concert: Concatenating a value with another value
decreases its length...

--
Greetings from Troels Arvin, Copenhagen, Denmark

Troels Arvin wrote:
> Hello,
>
> I read about the padding-of-CHAR-values changes in the release notes for
> 7.4.
>
> Making PostgreSQL less standard compliant is sad; I also disagree
> with the statement that trimming of trailing white-space is what people
> expect.
>
> What's worse, and this may be classified as an error:
>
> create table chartest(col char(10) not null);
> insert into chartest values ('AAA');
>
> select character_length(col) from chartest;
>  character_length
> ------------------
>                10
>
> select character_length(col || 'hey') from chartest;
>  character_length
> ------------------
>                 6
>
> SELECT CHARACTER_LENGTH(col) <
>   CHARACTER_LENGTH(col||'hey') from chartest;
>  ?column?
> ----------
>  f
>
> The last two results are horrifying, in my opinion, especially when you
> consider them in concert: Concatenating a value with another value
> decreases its length...

Horrifying?

Anyway, what did you want it to output?  "AAA       hey"?  We could do
that, but I assume most people wouldn't expect that output?  If you use
literals it does work:

    test=> SELECT 'aaa   ' || 'bb';
     ?column?
    ----------
     aaa   bb
    (1 row)

I tried this and it clipped too:

    test=> SELECT CAST('aa   ' AS CHAR(10)) || 'b';
     ?column?
    ----------
     aab
    (1 row)

How do other databases handle this?

--
  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

Bruce said:
> How do other databases handle this?

I have tried on MS SQL Server 2000 and Oracle 9i for Windows.
SQL Server doesn't like character_length and || , so use len and + instead.
Oracle doesn't like character_length either, use length.
Hope the result may help.

create table chartest(col char(10) not null);
insert into chartest values ('AAA');

PostgreSQL:
select character_length(col) from chartest;
10
SQL Server
select len(col) from chartest;
3
Oracle
select length(col) from chartest;
10

PostgreSQL:
select character_length(col || 'hey') from chartest;
6
SQL Server:
select len(col + 'hey') from chartest;
13
Oracle:
select length(col || 'hey') from chartest;
13

PostgreSQL:
select 'aaa   ' || 'bb';
aaa   bb
SQL Server:
select 'aaa   ' + 'bb';
aaa   bb
Oracle:
select 'aaa   ' || 'bb' from dual;
aaa   bb

PostgreSQL:
select cast('aa   ' as char(10)) || 'b';
aab
SQL Server:
select cast('aa   ' as char(10)) + 'b';
aa        b
Oracle:
select cast('aa   ' as char(10)) || 'b' from dual;
aa        b

On Wed, 19 Nov 2003 14:50:24 -0500, Bruce Momjian wrote:

> Anyway, what did you want it to output?  "AAA       hey"?  We could do
> that, but I assume most people wouldn't expect that output?

I certainly depends on their background. Personally, the padding
characteristics of the CHAR type was one of the first things about SQL
that I learned (the hard way). Oracle and DB2 people should be used to
PostgreSQL's old behaviour.

The CHAR type may seem strange to some, but they may then just
use VARCHAR.

> How do other databases handle this?

I've started writing about it here:
http://troels.arvin.dk/db/rdbms/#data_types-char

Some of my test-material is also online:
http://troels.arvin.dk/db/tests/chartest-20031119a/

My summary:

With regard to CHAR-handling, PostgreSQL 7.4 is now in opposition to
 - previous versions of PostgreSQL; bad enough on its own,
   because there doesn't seem to have been a good discussion
   about it first - I can only find a few messages about it [1]
 - DB2
 - Oracle
 - MSSQL (which also behaves in a non-standard way,
   but different from PostgreSQL 7.4)
7.4 is close to how MySQL works, though.

I'm sorry about not testing this before 7.4 went gold, but I believe that
this is a bug which should be corrected before too much confusion
is created.


Reference 1:
An interesting one is this one:
http://article.gmane.org/gmane.comp.db.postgresql.devel.general/10958/match=char+padding

--
Greetings from Troels Arvin, Copenhagen, Denmark