CREATE_COLLATION(7pg13)

💖 CREATE COLLATION 💖

okay so this is the PostgreSQL 13.23 page for CREATE COLLATION, bestie — it's the glow-up your text sorting deserves ✨

---

🌸 NAME 🌸

CREATE_COLLATION — literally define a brand new collation. that's it, that's the vibe 💅

---

🎀 SYNOPSIS 🎀

CREATE COLLATION [ IF NOT EXISTS ] name (
    [ LOCALE = locale, ]
    [ LC_COLLATE = lc_collate, ]
    [ LC_CTYPE = lc_ctype, ]
    [ PROVIDER = provider, ]
    [ DETERMINISTIC = boolean, ]
    [ VERSION = version ]
)
CREATE COLLATION [ IF NOT EXISTS ] name FROM existing_collation

two whole ways to do it, pick your fighter 🫶

---

🦋 DESCRIPTION 🦋

CREATE COLLATION makes a new collation using the operating system locale settings you give it, or by copying an existing collation. no cap, that's the whole thing.

but real talk: to create a collation you must have CREATE privilege on the destination schema. no privilege, no collation, sorry bestie 😭

---

✨ PARAMETERS ✨

• IF NOT EXISTS — if a collation with the same name already exists, do not throw an error, okay? a notice gets issued instead. but ⚠️ heads up: there is no guarantee that the existing collation is anything like the one that would have been created. it's giving "we're just gonna pretend this is fine" energy 💀
• name — the name of the collation. it can be schema-qualified; if it isn't, the collation lands in the current schema. the name must be unique within that schema. (the system catalogs can contain collations with the same name for other encodings, but those get ignored if the database encoding doesn't match. iykyk.)
• locale — this is the shortcut for setting LC_COLLATE and LC_CTYPE at once 🎀 if you specify this, you cannot specify either of those two parameters. pick a lane.
• lc_collate — use the specified operating system locale for the LC_COLLATE locale category.
• lc_ctype — use the specified operating system locale for the LC_CTYPE locale category.
• provider — tells it which provider to use for the locale services tied to this collation. possible values: icu, libc. libc is the default. which choices you actually get depends on the operating system and the build options, so vibe check your setup first.
• DETERMINISTIC — whether the collation should use deterministic comparisons. default is true. a deterministic comparison treats strings that aren't byte-wise equal as unequal even if the comparison considers them logically equal. PostgreSQL breaks ties using a byte-wise comparison. a comparison that's not deterministic can make the collation, say, case- or accent-insensitive 🙈 for that you need to pick an appropriate LOCALE setting and set the collation to not deterministic here.

⚠️ Nondeterministic collations are only supported with the icu provider. that's the rule, no exceptions.

• version — the version string to store with the collation. normally you should just omit this, which makes the version get computed from the actual version of the collation as provided by the operating system. this option exists so pg_upgrade can copy the version from an existing installation 👑

also go peek at ALTER COLLATION (ALTER_COLLATION(7)) for how to deal with collation version mismatches, ok?

• existing_collation — the name of an existing collation to copy. the new collation gets the same properties as the existing one, but it's a fully independent object. main character energy, detached from its origin 💅

---

🔥 NOTES 🔥

CREATE COLLATION takes a SHARE ROW EXCLUSIVE lock — which is self-conflicting — on the pg_collation system catalog. translation: only one CREATE COLLATION command can run at a time. queue up, besties 🧚

use DROP COLLATION to remove user-defined collations.

see Section 23.2.2.3 for more info on how to create collations 📖

when you're using the libc collation provider, the locale must be applicable to the current database encoding. see CREATE DATABASE (CREATE_DATABASE(7)) for the precise rules. don't skip this one, fr fr.

---

🌈 EXAMPLES 🌈

creating a collation from the operating system locale fr_FR.utf8 (assuming the current database encoding is UTF8):

CREATE COLLATION french (locale = 'fr_FR.utf8');

creating a collation with the ICU provider using German phone book sort order:

CREATE COLLATION german_phonebook (provider = icu, locale = 'de-u-co-phonebk');

creating a collation from an existing collation:

CREATE COLLATION german FROM "de_DE";

this one is lowkey convenient — it lets you use operating-system-independent collation names in your applications. ate. 💖

---

⭐ COMPATIBILITY ⭐

the SQL standard does have a CREATE COLLATION statement, but it's limited to copying an existing collation. the syntax for creating a brand-new collation is a PostgreSQL extension. we simply do more 💅

---

👑 SEE ALSO 👑

ALTER COLLATION (ALTER_COLLATION(7)), DROP COLLATION (DROP_COLLATION(7))

---

that's the whole page, bestie 🫶 huge thanks to the PostgreSQL 13.23 crew — page dated 2025, and yeah, the footer literally says CREATE COLLATION(7) one more time for good measure. go forth and collate iconically ✨