Thread: Problem with upper() in select statement

Greetings,

I'm having problems with what I believe to be a simple query and I assume
that either there is a bug in PostgreSQL in handling functions during a
select or I'm doing something very stupid.

I have a table of words and abbreviations where the word is being
used as a primary key. Everything in this table is stored in upper case
to make searches simple (don't have to worry about mixed case. Just convert
the word being searched for to upper and you're good to go).

Here is a simple sample session using psql to show the table structure
and two queries.

Why isn't the upper() function working in the first query?

Later,
John Cochran

----------------------------

bash-2.03$ psql dt
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

dt=> \d abbreviation
      Table "abbreviation"
 Attribute |   Type   | Modifier
-----------+----------+----------
 word      | char(15) | not null
 abbr      | char(4)  |
Index: abbreviation_pkey

dt=> select * from abbreviation where word = upper('Boulevard');
 word | abbr
------+------
(0 rows)

dt=> select * from abbreviation where word = 'BOULEVARD';
      word       | abbr
-----------------+------
 BOULEVARD       | BLVD
(1 row)

dt=> \q

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?

            regards, tom lane

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