Re: Problem with upper() in select statement

John Cochran <jdc(at)fiawol(dot)org> writes:
>> John Cochran <jdc(at)fiawol(dot)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