problem with spanish characters

Hello everybody,

I'm not sure if this is a bug on the upper/lower command or anywhere else.

This is the problem:

I have a DB that uses 'UNICODE' encoding. PostgreSQL version is 7.4.2.

I have a field that holds characters, including characters like 'Ñ' (lowercase version is 'ñ'). The name of the field is 'ccaa_ds_ccaa' (table name is 'ccaa').

When I execute the following SQL statement:

select * from ccaa where ccaa_ds_ccaa like '%Ñ%'

.. it returns one record (that's the correct result). There is only one record that matches this criteria (the actual value is 'CATALUÑA').

However, if I execute this one:

select * from ccaa where lower(ccaa_ds_ccaa) like '%Ñ%'

... it returns the very same record, which is WRONG, since the "lower" should make that query return no matching records.

Similarly, I tried this:

select * from ccaa where upper(ccaa_ds_ccaa) like upper(%ñ%')

... and IT DOES NOT RETURN ANY MATCHING RECORDS (it should return the same record return by the previous query).

It seems like the upper / lower commands do not work properly on UNICODE.

I tested this using 'LATIN9' encoding an it works properly in all cases.

Is this a known bug? Is there a bug fix for it? Where can I find it?

What effect does using UNICODE / LATIN9 on BLOB fields (bytea)? (I believe there are issues with the JDBC drivers)

Regards,

Freddy.