Re: Problem with upper() in select statement

John Cochran <jdc@fiawol.org> writes:
>> John Cochran <jdc@fiawol.org> writes:
>>>> Why isn't the upper() function working in the first query?
>>
>> Odd.  You aren't by some chance working in a non-ASCII locale where
>> "upper('Boulevard')" yields something besides 'BOULEVARD', are you?

> Nope, using the standard locale. Here is a short session to
> prove that upper() is working like it should.

Oh, never mind.  I was busy looking for complicated answers, but
actually the answer is simple: char(n) and text are not the same
thing because they have different ideas about the significance of
trailing blanks.

create table abbreviation(word char(15) not null);

insert into abbreviation values('BOULEVARD');

select * from abbreviation where word = 'BOULEVARD';
      word
-----------------
 BOULEVARD
(1 row)

The above works because the unknown-type literal 'BOULEVARD' is promoted
to char(n) type, and then char(n) vs. char(n) does what you want because
it regards trailing spaces as insignificant: 'BOULEVARD      ' is equal
to 'BOULEVARD' under char(n) rules.

But 'BOULEVARD      ' != 'BOULEVARD' under varchar(n) or text rules.
Thus this doesn't match:

select * from abbreviation where word = 'BOULEVARD'::text;
 word
------
(0 rows)

because the type ordering is that char(n) promotes to text not vice
versa, so you get a text equality comparison here.  Same result with

select * from abbreviation where word::text = 'BOULEVARD';
 word
------
(0 rows)

and more to the point, upper() is a function that yields type text,
so:

select * from abbreviation where word = upper('Boulevard');
 word
------
(0 rows)

You could make it work by coercing upper()'s result back to char(n),
so that char(n) equality is used:

select * from abbreviation where word = upper('Boulevard')::char;
      word
-----------------
 BOULEVARD
(1 row)

but on the whole my advice is that you are using the wrong datatype for
this table.  Variable-length strings should be represented by varchar(n)
or text.  Fixed-width char(n) is appropriate for fixed-length strings
like state abbreviations.


> BTW, why doesn't PostgreSQL have a SYSDUMMY table or something like it
> (the way Oracle or IBM's DB2 have).

Don't need it, since we don't require a FROM clause.

regression=# select upper('Boulevard');
   upper
-----------
 BOULEVARD
(1 row)

            regards, tom lane