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


Your name : Flavio
Your email address : fcasadei (AT) prato (DOT) 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!



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

"Flavio Casadei D. C." <f.casadeidellachiesa (AT) comune (DOT) prato.it> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org