Thread: Bug on CHARACTER(n) ?

Hello list,
maybe I am missing something. Look at this:

CREATE TABLE test (name character(10));

INSERT INTO test VALUES ('test');

SELECT '<' || name || '>' FROM test;

  ?column?
----------
  <test>

SELECT length(name), name from test;
  length |    name
--------+------------
       4 | test


Should not a char(10) insert 6 padding spaces? Or am I missing something
like an update in the SQL standard?
Sorry if this is an already known issue, I had no time lately to check the
bugs mailing list.

Ciao,
   Stefano

On Mon, Nov 28, 2005 at 05:49:02PM +0100, Stefano Reksten wrote:
> Should not a char(10) insert 6 padding spaces? Or am I missing something
> like an update in the SQL standard?
char_length()

regards
--
Pawe³ Bernat; uselessness' lover;
select'<asm'||chr(64)||'asm'||'.'||'flynet'||chr(46)||'pl>'as email;
Slowly and surely the unix crept up on the Nintendo user ...

Stefano Reksten <sreksten@sdb.it> writes:
> SELECT length(name), name from test;
>   length |    name
> --------+------------
>        4 | test

> Should not a char(10) insert 6 padding spaces?

It does, as you can see in the above output; but the padding spaces are
considered semantically insignificant and are therefore ignored by
length() and by concatenation.  If you think that trailing spaces are
meaningful data, then use varchar(n) or text.

            regards, tom lane

On Mon, Nov 28, 2005 at 05:49:02PM +0100, Stefano Reksten wrote:
> Should not a char(10) insert 6 padding spaces? Or am I missing something
> like an update in the SQL standard?
Use char_length(string).

regards
--
Pawe³ Bernat; uselessness' lover;
select'<asm'||chr(64)||'asm'||'.'||'flynet'||chr(46)||'pl>'as email;
Slowly and surely the unix crept up on the Nintendo user ...