Re: whitespaces and upper()

"Flavio Casadei D. C." <f(dot)casadeidellachiesa(at)comune(dot)prato(dot)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