Forum OpenACS Development: Response to PostgreSQL upper() / lower() functions and LATIN2 charset
Thanx for your responses but I think, I should explain my problem again.
I want to use (incoming) OpenACS 4.x with LATIN2 encoding.
In my opinion, I should set database encoding to UNICODE.
So, I have to compile PostgreSQL 7.1 with --enable-multibyte and
(and, maybe, with --enable-locale).
Next initdb -E UNICODE -D /usr/local/pgsql/data .
Am I right?
As I wrote in my first post, upper() and lower() works improperly with these settings. But not only upper() and lower() works improperly. Also ORDER BY works improperly.
I test everything in this way:
- First, I create table
CREATE TABLE names ( name varchar(20) );
- Next, I insert a few names via browser (encoded in ISO8859-2) to tcl script;
- The Tcl script encodes the names to UTF-8 (UNICODE) and sends them to PostgreSQL.
- Then, PostgreSQL sends results of query
SELECT UPPER(name) AS fn FROM names ORDER BY fn;
(encoded in UNICODE) to the Tcl script.
- The script encodes the results to ISO8859-2 and sends then to browser.
With this query it works improperly, but when I try with query SELECT name FROM names;, results are OK.
It looks PostgreSQL doesn't know, how to sort query results, so it use default sorting method (based on order of inserting records?).,
I'm not sure, it's PostgreSQL bug. Maybe it's problem with my locale settings (LC_ALL=pl_PL, LC_COLLATE=pl_PL , LC_CTYPE=pl_PL, LANG=pl_PL ) or with C library (glibc 2.1).
I have Debian GNU/Linux 2.2 "Potato" - Intel Celeron - kernel 2.2.19
PostgreSQL compiled with gcc 2.95.2