3 ms·

It appears work is active (May 2017) in this area, but accent-insensitive collations are still a ways off: https://blog.2ndquadrant.com/icu-support-postgresql-1

by claar 9y ago

It appears work is active (May 2017) in this area, but accent-insensitive collations are still a ways off: https://blog.2ndquadrant.com/icu-support-postgresql-10/ https://blog.2ndquadrant.com/icu-support-postgresql-10/ edit: fulafel corrects me below that you can get accent-insensitive collations working using PostgreSQL 10's new ICU collation support. Note that PostgreSQL 10 is currently pre-release.

fulafel 9y ago
That post says that they currently work, but there is a new os-independent collation method coming. Edit re your edit: the already working collations are in PG 9.x - the OS independent collations are coming in 10.x. (Hence the post title, "More robust collations with ICU support in PostgreSQL 10")
- fauigerzigerk 9y ago
  But do these "already working" collations include accent-insensitive variants? I didn't think so, and I don't think they are coming in PostgreSQL 10 either. Do you have other information on this?
  - petergeoghegan 9y ago
    They do. The problem is that equality is still strict binary equality for text, for obscure technical reasons. So, while you can get extremely flexible customizations to collation with icu [1], you can't have case insensitive equality, or equality that ignores accents. This is recognized as a problem, and is probably going to be fixed in v11. [1] https://www.postgresql.org/message-id/CAH2-Wz=bcgmk97YaZ3rs4OoCdn1nOco1HCfRGBCOOty0ztnCnA@mail.gmail.com https://www.postgresql.org/message-id/CAH2-Wz=bcgmk97YaZ3rs4...
    - anarazel 9y ago
      > The problem is that equality is still strict binary equality for text, for obscure technical reasons AKA performance.
  - fulafel 9y ago
    My misunderstanding seemed to be that "accent-insensitive" turns out to be a precise technical term from MySQL that says "just sort UTF-8 using the American locale and collation, but pretend accented characters are equal to their non-accented versions" which is not a standard collation order in any locale.
    - berns 9y ago
      I think this is incorrect. In Mysql utf8_spanish_ci and utf8_swedish_ci have different accent rules, for example.
      - fulafel 9y ago
        But then what would be the case for using accent-ignoring spanish collation rules, rather than the default spanish collation rules that sort accented chars according to normal spanish sorting? This sounds strictly inferior in any human facing application.
  - deleted 9y ago
    [deleted]

by claar 9y ago

fulafel 9y ago

That post says that they currently work, but there is a new os-independent collation method coming. Edit re your edit: the already working collations are in PG 9.x - the OS independent collations are coming in 10.x. (Hence the post title, "More robust collations with ICU support in PostgreSQL 10")

fauigerzigerk 9y ago
But do these "already working" collations include accent-insensitive variants? I didn't think so, and I don't think they are coming in PostgreSQL 10 either. Do you have other information on this?
- petergeoghegan 9y ago
  They do. The problem is that equality is still strict binary equality for text, for obscure technical reasons. So, while you can get extremely flexible customizations to collation with icu [1], you can't have case insensitive equality, or equality that ignores accents. This is recognized as a problem, and is probably going to be fixed in v11. [1] https://www.postgresql.org/message-id/CAH2-Wz=bcgmk97YaZ3rs4OoCdn1nOco1HCfRGBCOOty0ztnCnA@mail.gmail.com https://www.postgresql.org/message-id/CAH2-Wz=bcgmk97YaZ3rs4...
  - anarazel 9y ago
    > The problem is that equality is still strict binary equality for text, for obscure technical reasons AKA performance.
- fulafel 9y ago
  My misunderstanding seemed to be that "accent-insensitive" turns out to be a precise technical term from MySQL that says "just sort UTF-8 using the American locale and collation, but pretend accented characters are equal to their non-accented versions" which is not a standard collation order in any locale.
  - berns 9y ago
    I think this is incorrect. In Mysql utf8_spanish_ci and utf8_swedish_ci have different accent rules, for example.
    - fulafel 9y ago
      But then what would be the case for using accent-ignoring spanish collation rules, rather than the default spanish collation rules that sort accented chars according to normal spanish sorting? This sounds strictly inferior in any human facing application.
- deleted 9y ago
  [deleted]