8.0b3: problems with ILIKE and ~* on multibyte-chars

8.0 beta3


the caseinsensitive patternmatch-operators seems not to work with
multibyte, while lower() and upper()-functions finally works perfect.

provided the correct locale-setting on initdb (LC_CTYPE=de_AT.UTF-8 in
the example) the following happens:


example :

test=# \l
         List of databases
    Name    |  Owner   | Encoding
-----------+----------+-----------
  test      | postgres | UNICODE


test=# \d test
     Table "public.test"
  Column | Type | Modifiers
--------+------+-----------
  t      | text |
Indexes:
     "t_idx" btree (t)


test=# select t from test;
  t
---
  ä
  Ä
  o
  O
(4 rows)


test=# select t from test where t~*'ä';
  t
---
  ä
(1 row)

====> expected is to give 'ä' and 'Ä' as result, cause search is
caseinsesitive and 'Ä' is uppercase of 'ä'


test=# select t from test where t~*'Ä';
  t
---
  Ä
(1 row)

======> same as above


test=# select t from test where  lower(t)~'ä';
  t
---
  ä
  Ä
(2 rows)


=====> the correct result using a workaround



test=# select t from test where t~*'o';
  t
---
  o
  O
(2 rows)

======> it works with "normal" chars



# /usr/local/pgsql8/bin/pg_controldata /data/postgres/postgres8/ | grep LC
LC_COLLATE:                           de_AT.UTF-8
LC_CTYPE:                             de_AT.UTF-8



general feedback on multibytes:

things work just great. I put 8.0b3 in productional for my
unicode-databases two days ago, cause lower() finally works. No problems
so far. Things work just great. THNX A LOT !!!

a big featurerequest is different locales for different databases.  A
locale LC_COLLATE "de_AT.UTF-8" simply makes no sense for
non-unicode-databases and gives very odd results !!

If one wants to use locales and unicode, he needs to run two instances
of postgres. One for his unicodedatabases on one for its standard
databases to get correct sorting in his locale.


GREAT JOB !! THNX A LOT !!

peter



--
mag. peter pilsl
goldfisch.at
IT-management
tel +43 699 1 3574035
fax +43 699 4 3574035
pilsl@goldfisch.at