Re: case insensitive collation of Greek's sigma - Mailing list pgsql-general

I realise this may not be applicable to the original problem, but
non-deterministic collations seems to offer a solution::


  dakkar@[local] dakkar=> create collation "en-US-ins-icu" (
         provider=icu,
         locale='en-US-u-ks-level2',
         deterministic=false
         );

  dakkar@[local] dakkar=> select 'ΣΣ' = 'σσ' collate "en-US-ins-icu";
  ┌──────────┐
  │ ?column? │
  ├──────────┤
  │ t        │
  └──────────┘
  (1 row)

  dakkar@[local] dakkar=> select 'ΣΣ' = 'σς' collate "en-US-ins-icu";
  ┌──────────┐
  │ ?column? │
  ├──────────┤
  │ t        │
  └──────────┘
  (1 row)

  dakkar@[local] dakkar=> select 'ΣΣ' = 'α' collate "en-US-ins-icu";
  ┌──────────┐
  │ ?column? │
  ├──────────┤
  │ f        │
  └──────────┘
  (1 row)

Notice, though:

* I don't understand what that ``-u-`` is doing in ``locale``, but
  it's necessary
* as the docs
  https://www.postgresql.org/docs/13/collation.html#COLLATION-NONDETERMINISTIC
  say:

  - B-tree cannot use deduplication with indexes that use a
    nondeterministic collation
  - certain operations are not possible with nondeterministic
    collations, such as pattern matching operations (this means you
    can't use ``LIKE``)

--
    Dakkar - <Mobilis in mobile>
    GPG public key fingerprint = A071 E618 DD2C 5901 9574
                                 6FE2 40EA 9883 7519 3F88
                        key id = 0x75193F88