SQL Server collation and its equivalent in Postgres

Question

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?

Answer 1

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
);