The following bug has been logged online:
Bug reference: 5780
Logged by: Thomas Bakketun
Email address: thomasb@copyleft.no
PostgreSQL version: 8.4.5
Operating system: Ubuntu 10.04.1 LTS
Description: Case insensitive regular expressions
Details:
Apparently case insensitivity in regular expressions only works for ASCII
characters.
ksys@nallemaja:~$ psql -l
List of databases
Name | Owner | Encoding | Collation | Ctype | Access
privileges
-----------+----------+----------+-------------+-------------+--------------
---------
ksys | ksys | UTF8 | nb_NO.UTF-8 | nb_NO.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
s2ksys | ksys | UTF8 | nb_NO.UTF-8 | nb_NO.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
:
postgres=CTc/postgres
template1 | postgres | UTF8 | nb_NO.UTF-8 | nb_NO.UTF-8 |
template2 | postgres | UTF8 | nb_NO.UTF-8 | nb_NO.UTF-8 |
test1 | ksys | UTF8 | nb_NO.UTF-8 | nb_NO.UTF-8 |
(7 rows)
ksys@nallemaja:~$ psql ksys
psql (8.4.5)
Type "help" for help.
ksys=> select 'Ãst' ilike 'øst', 'Ãst' ~* 'øst';
?column? | ?column?
----------+----------
t | f
(1 row)
The different behavior of ilike and case insensitive regular expressions is
surprising. I would have expected both to return true. The documentation
makes it clear ilike depends on the current locale, but not so regarding
regular expressions.