Hacker News Reader

Comment by jvdongen | Hacker News Reader

For my multi-lingual-content-needs I work with a main table which only has the non-translatable data items and a subtable which contains the translatable content, one row per language per row from the main table. Then I do a join to get a single full 'object' in the language of choice. And per the postgres docs I can choose collation at query time. Thus the following query would do the trick if I'm correct:

select * from main_table join sub_table on main_id=sub_id where sub_table.language='fr_FR' ORDER BY my_translatable_col COLLATE "fr_FR.utf8";

No bloating tables, just a join and correct collation.

View on HN

Most content is not "translated", it just needs to be differently collated. If you have a company directory, you have the names of every user in the company in a table, and you need to display that information in a collation based on the locale of the viewing user. Having to have a new table for every language that contains the same data as the main table is just pointless overhead. Also, while you can choose the collation at query time, the point of per-column collation is to let you have an index over that collation. Can you please demonstrate how the use case of "company directory" would be cleanly and efficiently implemented using per-column collation?

EDIT: I've been looking more into this COLLATE keyword that they have added, and I'm actually somewhat curious to see if I can make this work (where the optimizer manages to choose the right index) by something like the following, in which case I'm going to be seriously happy... ;P.

    CREATE TABLE my_table (a text);
    CREATE INDEX my_index ON my_table ((a COLLATE "en_US"));
    CREATE INDEX my_index ON my_table ((a COLLATE "de_DE"));
    SELECT * FROM my_table ORDER BY a COLLATE "de_DE";

Simply out of curiosity for this same topic, do you happen to know of a good resource for finding out even just some of the less trivial differences that this solves? I'm sure it does but off hand I don't know them (I'm not all that multilingual).

I understand it'll bring in glyph orderings that don't exist in en_US or whatever you've got the default set to, such as 'Ç' in french among others.

I do not have a good resource, however, I know a few off the top of my head: 1) characters with modifiers, like umlauts, sometimes collate the same, and sometimes collate differently; 2) multiple characters may collate as a single character, such as "ll" (I just did a search to verify that this was the case in Spanish, and found the Collation page on Wikipedia, which you might find interesting); and 3) different locales may choose to collate numbers using different algorithms (in English we usually expect "1,000" to sort after "200", but if "," is a decimal point, then you might not).

Reply on news.ycombinator.com