Thread: whitespaces and upper()

============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name        :     Flavio
Your email address    :    fcasadei@prato.linux.it


System Configuration
---------------------
  Architecture (example: Intel Pentium)      : Intel pantium & Motorola PPC

  Operating System (example: Linux 2.4.18)     : linux 2.4.{18,21,26} 2.6.5 and darvin 7.0

  PostgreSQL version (example: PostgreSQL-7.4.2):   PostgreSQL-7.4.2 and 7.4.1

  Compiler used (example:  gcc 2.95.2)        : various


Please enter a FULL description of your problem:
------------------------------------------------

Hi,
i have a problem with upper() / lower() and "whitespace" conversion,
this is a 7.4.* behaviour; string with whitespaces are not correctly
"matched".




Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------
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)
------->(righe means row)<---------

test=> SHOW  client_encoding ;
 client_encoding
 -----------------
  LATIN1
  (1 riga)

test=> SHOW  server_encoding ;
 server_encoding
 -----------------
  LATIN1
  (1 riga)

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

----> Strange no?  look at this (no upper)<----
test=> SELECT * from foo where name like '% ';
 id |  name  | value
 ----+--------+-------
   2 | FLA    | hi!
   3 |        | hi!
   4 |    d   | hi!
   (3 righe)
that's ok
---->look at this now .... (ilike instead of like) <----
test=> SELECT * from foo where name ilike 'f% ';
 id |  name  | value
 ----+--------+-------
   2 | FLA    | hi!
(1 riga)
test=> SELECT * from foo where name ilike '% ';
 id |  name  | value
 ----+--------+-------
   2 | FLA    | hi!
   3 |        | hi!
   4 |    d   | hi!
   (3 righe)

i think there is a problem with upper() and lower() whitespace  conversion
the problem arises also if i use =  instead of like.

i tried this on
7.4.1 red hat 7.3
7.4.2 debian sid
7.4.2 mac os X
the problem persists also if i change the encoding in unicode.

Is a bug? or a "normal behaviour"?

Ciao


If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------
don't know, i wonder wy ilike works and upper() (and lower!!) fail.
With 7.3 is all ok!

"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