Hey guys,
I'm trying to adopt an application to use PostgresQL. Its a ruby on rails app and with ActiveRecord, the transition is mostly straight forward. There is one issue though where I got stuck, perhaps you can help:
One aspect of the app is to store international items. For example a table "people" might contain "Albrecht Dürer" and "Paul Cézanne" in the same column. Now, with MySQL, all of the following have at least one result:
SELECT * FROM people WHERE name LIKE '%albrecht%';
SELECT * FROM people WHERE name LIKE '%Anne%';
SELECT * FROM people WHERE name LIKE '%durer%';
SELECT * FROM people WHERE name LIKE '%DÜRER%';
SELECT * FROM people WHERE name LIKE '%cezanne%';
SELECT * FROM people WHERE name LIKE '%Álbrecht%';
I owe that to the collation utf8_general_ci which seems to reasonably fold a wide range of characters.
For PostrgesQL, I'm using the official docker container:
and I tried to use the provided hook to essentially run the following bash commands:
echo "en_US.UTF-8 UTF-8" >> /etc/locale.gen
echo "de_DE.UTF-8 UTF-8" >> /etc/locale.gen
locale-gen
AFTER the cluster is created. So I understand that initdb uses en_US.UTF-8 for a locale. I also went ahead and:
CREATE COLLATION german (LOCALE = 'de_DE.utf8');
My problem is that I don't (even) get the german collation to work, the following query yields 'f':
SELECT 'a' COLLATE "german" LIKE 'ä' COLLATE "german";
Shouldn't that be working? Also, the way I understand the docs, there is not really a way to collate with multiple locales on the same field? This would mean that there is no equivalent of utf8_general_ci in PostgresQL?
I hope I'm clear and thanks for any help!
Moritz
