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
