Thread: weird lower() problem with character

Hi,

it seems that something has changed in handling 'character' datatypes.

With 7.2.3 this worked fine, with 7.3.1 it does not work.

This is a test with varchar, and it works:
==============================================================
test=> create table test ( test varchar(200) );
CREATE TABLE
test=> insert into test values ('test@test.com');
INSERT 12016215 1
test=> select * from test where test = lower('TEST@TEST.COM');
     test
---------------
 test@test.com
(1 row)
==============================================================

with 'character' it does not work anymore:
==============================================================
test=> create table test ( test character(200) );
CREATE TABLE
test=> insert into test values ('test@test.com');
INSERT 12016228 1
test=> select * from test where test = lower('TEST@TEST.COM');
 test
------
(0 rows)
==============================================================

Comments? How can I fix that without converting to varchar?

Regards,
Bjoern

=?iso-8859-1?Q?Bj=F6rn_Metzdorf?= <bm@turtle-entertainment.de> writes:
> With 7.2.3 this worked fine, with 7.3.1 it does not work.

Your memory is playing tricks on you.  That behavior is the same as it
ever was (or at least, the same as it was back to 7.0, the oldest
version I have handy to try your example on).


regression=# create table test ( test character(200) );
CREATE
regression=# insert into test values ('test@test.com');
INSERT 797819 1
regression=# select * from test where test = lower('TEST@TEST.COM');
 test
------
(0 rows)

regression=# select version();
                            version
---------------------------------------------------------------
 PostgreSQL 7.2.3 on hppa-hp-hpux10.20, compiled by GCC 2.95.3
(1 row)


> Comments? How can I fix that without converting to varchar?

Convert.  char(n) is evil.  If I had my druthers, we'd take it out
of the language.

            regards, tom lane