2

Legacy system, backed by SQL Server, has unique indexes using collation Latin1_General_CP1_CI_AI. It compares things using charset Latin 1, encoded using CP-1252 (CP1 part), case insensitive, accent insensitive.

Which collation should I use in Postgres to get exactly the same behavior?

3
  • 2
    Maybe you could explain what behaviour that is, since Postgres experts who could answer the question might not know what the specified collation does in SQL Server...
    – mustaccio
    Jun 30, 2022 at 18:24
  • @mustaccio Done.
    – Ginden
    Jun 30, 2022 at 18:40
  • You'll need to check pg_collation to see the available collations. If none matches your needs, you can create your own ICO collation. There are several posts here and on stackoverflow.com on how to create a case-insensitive ICU collation
    – user1822
    Jun 30, 2022 at 19:23

1 Answer 1

3

You won't be able to create a collation that behaves absolutely the same. The best would probably be this ICU collation:

CREATE COLLATION general_ci_ai (
   PROVIDER = icu,
   DETERMINISTIC = FALSE,
   LOCALE = '@ColStrength=primary'
);

However, you cannot have an non-deterministic collation as database collation. You will have to use it explicitly in column definitions:

CREATE TABLE tab (
   col text COLLATE general_ci_ai
);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.