PostgreSQL Collations

PostgreSQL collations define locale-driven sort and comparison rules for text data. PostgreSQL supports multiple collation providers: ICU (International Components for Unicode), libc (platform-specific), and the default provider inherited from the database.

In SQL, you create custom collations like this:

CREATE COLLATION french (PROVIDER = icu, LOCALE = 'fr-x-icu');
CREATE COLLATION german_phonebook (PROVIDER = icu, LOCALE = 'de-u-co-phonebk');
CREATE COLLATION custom (PROVIDER = icu, LOCALE = 'und', RULES = '&V << w <<< W');

After a collation is created, you can reference it in column definitions:

CREATE TABLE t (
  name text COLLATE french,
  description text COLLATE german_phonebook
);

info

ICU locales use BCP 47 language tags with optional Unicode extension keywords:

• co-phonebk — Phonebook ordering (e.g., de-u-co-phonebk)
• kf-upper / kf-lower — Case-first sorting
• kn-true — Numeric string sorting (e.g., id-2 before id-10)

Some settings (like kf-upper) require DETERMINISTIC = false to take effect. Non-deterministic collations cannot be used in unique indexes or primary keys. Learn more in the PostgreSQL documentation.

Managing Collations with Atlas

Collations are schema-scoped objects. When you define them in HCL or SQL and reference them from columns, Atlas resolves the dependencies and produces the correct migration plan automatically.

HCL

collation "french" {
  schema   = schema.public
  locale   = "fr-x-icu"
  provider = icu
}

table "users" {
  schema = schema.public
  column "id" {
    type = serial
  }
  column "name" {
    type    = text
    collate = collation.french
  }
}

SQL

CREATE COLLATION "french" (PROVIDER = icu, LOCALE = 'fr-x-icu');

CREATE TABLE users (
  id serial,
  name text COLLATE french
);

Example: Adding a Collation to an Existing Column

Start with a table without collations:

schema.hcl

schema "public" {}

table "users" {
  schema = schema.public
  column "id" {
    type = serial
  }
  column "name" {
    type = text
  }
}

Add a French collation and apply it to the name column:

schema.hcl

schema "public" {}

collation "french" {
  schema   = schema.public
  locale   = "fr-x-icu"
  provider = icu
}

table "users" {
  schema = schema.public
  column "id" {
    type = serial
  }
  column "name" {
    type    = text
    collate = collation.french
  }
}

Apply the changes:

atlas schema apply \
  --url "postgres://postgres:pass@localhost:5432/dev?sslmode=disable" \
  --to "file://schema.hcl"

Atlas generates:

-- Create collation "french"
CREATE COLLATION "public"."french" (LOCALE = 'fr-x-icu', PROVIDER = icu);
-- Modify "users" table
ALTER TABLE "public"."users" ALTER COLUMN "name" TYPE text COLLATE "public"."french";

Atlas creates the collation before modifying the table, ensuring correct dependency ordering.

Need More Help?

Join the Atlas Discord Server for early access to features and the ability to provide exclusive feedback that improves your Database Management Tooling.