On Tue, Aug 29, 2006 at 09:27:59AM -0400, Tom Lane wrote: > Tino Schwarze <[EMAIL PROTECTED]> writes: > > On Sun, Aug 27, 2006 at 12:58:00PM +0000, Robert Siemer wrote: > >> I tried this with LC_COLLATE=C and the rest LC_...=es_ES.utf8 > >> > >> dennisb from irc reported LC_everything=sv_SE.UTF-8 with version 8.1.0 > >> having the same problems. > > > I can confirm this with de_DE.utf8. > > ilike currently doesn't work for multibyte encodings (eg utf8). This > bug has been known for a long while, eg, > > http://archives.postgresql.org/pgsql-bugs/2005-10/msg00002.php > > but no one's stepped up to fix it.
The "use lower() for both strings" solution sounds reasonably simple to me, but I'm not familiar with PgSQLs sources... The funny thing is: It currently works, if you set the locale to de_DE (non-UTF8)! pg_controldata output: LC_COLLATE: de_DE.utf8 LC_CTYPE: [EMAIL PROTECTED] Output from test database: test=# select * from test where test ilike '%ä%'; test --------- äbcd Äbbcd bläbbcd BLÄbbcd (4 rows) test=# select * from test where test ilike '%Ä%'; test --------- äbcd Äbbcd bläbbcd BLÄbbcd (4 rows) -> same result, both upper and lower ä match. test=# select * from test where test ilike '%ö%'; test ------ ö Ö (2 rows) test=# select * from test where test ilike '%Ö%'; test ------ ö Ö (2 rows) test=# select * from test order by test desc; test --------- Ü ü Ö ö MÜLLER müller BLÄbbcd bläbbcd afbcd aebcd äbcd abcd Äbbcd aabcd (14 rows) Everything is fine with this weird locale setting. (All other locales are set to C)... Bye, Tino. ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
