4 ms·

I use PostgreSQL but are not sure it was the right choice. In MySQL it is easy to set which collation to use for a query to get the correct order in the users l

I use PostgreSQL but are not sure it was the right choice. In MySQL it is easy to set which collation to use for a query to get the correct order in the users language when using order by. In PostgreSQL it depends on the locales installed on the OS making it much harder to get a correct order by and requires more work when setting up the servers

developer2 9y ago
For me this is the last major "missing piece" for postgres. MySQL's utf8mb4_unicode_ci makes it possible to order results containing a mix of any languages. With postgres you are forced to pick a single language. It's impossible to use postgres for a truly multi-language application, where you are presenting data in (for example) English, German and Chinese on the same UI. Yes, MySQL's ordering of languages within unicode may be arbitrary, not necessarily very meaningful, and in some cases even "wrong" - but at least you can do it, and the results are consistent and usable. Postgres is outright unusable in this scenario.
- Doctor_Fegg 9y ago
  For me, the last major missing piece for Postgres would be a silent, seamless pg_upgrade when you upgrade your OS.
- bmn__ 9y ago
  > MySQL's utf8mb4_unicode_ci makes it possible to order results containing a mix of any languages. With postgres you are forced to pick a single language. It's impossible to use postgres for a truly multi-language application That's not true.
  - developer2 9y ago
    If you know of a solution, please share. As far as I know, it is not possible to do multi-language unicode sorting in postgres. The idea being you have a utf8 column with some rows having English, while others have German, Chinese, Japanese, etc. Or an individual column containing mixed language, such as English and Chinese in a single string. And of course, the real deal - a consistent sort for all unicode points, including emotes and other non-language code points. In postgres you're stuck with a single-language collation such as en_US.utf8 - there's no multi-purpose "utf8" collation.
    - bmn__ 9y ago
      order by collkey(mixed_texts, 'root-u-co-search', true, 0, true)
jeltz 9y ago
PostgreSQL 10 will have support for ICU collations which should be much more stable across different installations. Since it is the first version it could have some rough edges, but I suggest giving it a spin.
- bmn__ 9y ago
  http://www.public-software-group.org/pg_collkey http://www.public-software-group.org/pg_collkey exists, first release in 2006 for PostgreSQL 8. Those who wanted Unicode-compliant collation support had already installed it years ago.

developer2 9y ago

For me this is the last major "missing piece" for postgres. MySQL's utf8mb4_unicode_ci makes it possible to order results containing a mix of any languages. With postgres you are forced to pick a single language. It's impossible to use postgres for a truly multi-language application, where you are presenting data in (for example) English, German and Chinese on the same UI. Yes, MySQL's ordering of languages within unicode may be arbitrary, not necessarily very meaningful, and in some cases even "wrong" - but at least you can do it, and the results are consistent and usable. Postgres is outright unusable in this scenario.

Doctor_Fegg 9y ago
For me, the last major missing piece for Postgres would be a silent, seamless pg_upgrade when you upgrade your OS.
bmn__ 9y ago
> MySQL's utf8mb4_unicode_ci makes it possible to order results containing a mix of any languages. With postgres you are forced to pick a single language. It's impossible to use postgres for a truly multi-language application That's not true.
- developer2 9y ago
  If you know of a solution, please share. As far as I know, it is not possible to do multi-language unicode sorting in postgres. The idea being you have a utf8 column with some rows having English, while others have German, Chinese, Japanese, etc. Or an individual column containing mixed language, such as English and Chinese in a single string. And of course, the real deal - a consistent sort for all unicode points, including emotes and other non-language code points. In postgres you're stuck with a single-language collation such as en_US.utf8 - there's no multi-purpose "utf8" collation.
  - bmn__ 9y ago
    order by collkey(mixed_texts, 'root-u-co-search', true, 0, true)

jeltz 9y ago

PostgreSQL 10 will have support for ICU collations which should be much more stable across different installations. Since it is the first version it could have some rough edges, but I suggest giving it a spin.

bmn__ 9y ago
http://www.public-software-group.org/pg_collkey http://www.public-software-group.org/pg_collkey exists, first release in 2006 for PostgreSQL 8. Those who wanted Unicode-compliant collation support had already installed it years ago.