Re: whitespaces and upper()

"Flavio Casadei D. C." <f.casadeidellachiesa@comune.prato.it> writes:
> create table foo (
> id integer not null primary key,
> name char(6) not null,
> value varchar(255)
> );
> insert into foo values (1,'FLAVIO','hi!');
> insert into foo values (2,'FLA   ' ,'hi!');
> insert into foo values (3,'      ','hi!');
> insert into foo values (4,'   d  ','hi!');
> test=> SELECT * from foo ;
>  id |  name  | value
>  ----+--------+-------
>    1 | FLAVIO | hi!
>    2 | FLA    | hi!
>    3 |        | hi!
>    4 |    d   | hi!
>  (4 righe)

> test=> SELECT * from foo where upper(name) like '% ';
>  id | name | value
>  ----+------+-------
>  (0 righe)

This is because upper() takes a text argument, and as of 7.4 char(N) to
text conversion discards trailing spaces.  I believe this behavior is
correct on balance: if you think that trailing spaces are significant
data, you ought to be storing the column as varchar or text, not char.

            regards, tom lane