CREATE_COLLATION(7pg18)

💖 CREATE_COLLATION — like, the manual page for PostgreSQL 18.6, bestie 💖

okay so welcome to the glow-up guide for CREATE COLLATION, straight out of the PostgreSQL 18.6 Documentation. grab a boba, we're defining collations today and it's gonna be iconic ✨

---

✨ NAME ✨

CREATE_COLLATION — define a new collation 💅

---

🦋 SYNOPSIS 🦋

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

---

🌸 DESCRIPTION 🌸

so CREATE COLLATION is literally how you make a brand new collation — either by pulling the operating system locale settings, or by copying a collation that already exists. both are valid, both are cute 💖

and before you get any ideas: to create a collation, you must have CREATE privilege on the destination schema. no privilege, no collation, periodt.

---

🎀 PARAMETERS 🎀

• IF NOT EXISTS — bestie, this is the "no drama" flag. if a collation with the same name is already out there, don't throw an error, just send a notice instead. but heads up: there's no guarantee that the existing collation is anything like the one you were trying to make. it might be a whole different vibe. 🙈
• name — the name of the collation, obviously 💅 it can be schema-qualified, and if it's not, the collation gets defined in the current schema. the name has to be unique within that schema. (fun fact: the system catalogs can hold collations with the same name for other encodings, but those get ignored if the database encoding doesn't match — so don't stress about them.)
• locale — the locale name for this collation. peep Section 23.2.2.3.1 and Section 23.2.2.3.2 for the details, they go deep.

if provider is libc, this is a shortcut for setting LC_COLLATE and LC_CTYPE at the same time — two birds, one stone, slay. and if you specify locale, you cannot specify either of those two parameters. pick a lane, queen.

if provider is builtin, then locale must be specified, and it must be set to either C, C.UTF-8 or PG_UNICODE_FAST. those are the only options, no substitutions.

• lc_collate — if provider is libc, use the specified operating system locale for the LC_COLLATE locale category.
• lc_ctype — if provider is libc, use the specified operating system locale for the LC_CTYPE locale category.
• provider — tells you which provider handles the locale services for this collation. possible values are builtin, icu (only if the server was built with ICU support) or libc. libc is the default, it's the main character. see Section 23.1.4 for details.
• DETERMINISTIC — whether the collation uses 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 with a byte-wise comparison. a comparison that's not deterministic can make the collation, like, case-insensitive or accent-insensitive, which is kinda the whole point. for that you need to choose 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 cap.

• rules — extra collation rules to customize the behavior of the collation. ICU only, bestie. see Section 23.2.3.4 for details.
• version — the version string to store with the collation. normally you should just… not. omitting it makes the version get computed from the actual version of the collation as provided by the operating system, which is usually what you want. this option exists for pg_upgrade to copy the version from an existing installation.

also see ALTER COLLATION (ALTER_COLLATION(7)) for how to deal with collation version mismatches 👀

• existing_collation — the name of an existing collation to copy. the new collation will have all the same properties as the existing one, but it's an independent object. it's giving twin energy, not clone energy 🫶

---

💜 NOTES 💜

CREATE COLLATION takes a SHARE ROW EXCLUSIVE lock on the pg_collation system catalog, and that lock is self-conflicting — so only one CREATE COLLATION command can run at a time. one at a time, we wait our turn, it's called manners 💅

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 — they are precise, fr fr.

---

🔥 EXAMPLES 🔥

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

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

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

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

making a collation with the ICU provider, based on the root ICU locale, with custom rules:

CREATE COLLATION custom (provider = icu, locale = 'und', rules = '&V << w <<< W');

see Section 23.2.3.4 for further details and examples on the rules syntax 📚

making a collation from an existing collation:

CREATE COLLATION german FROM "de_DE";

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

---

🌈 COMPATIBILITY 🌈

there is a CREATE COLLATION statement in the SQL standard, bestie, but it's limited to copying an existing collation only. the syntax for creating a brand new collation? that's a PostgreSQL extension. we invented that. 💅

---

⭐ SEE ALSO ⭐

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

---

and that's the whole page, bestie! 🎀 this was PostgreSQL 18.6, dated 2026, and it ATE. go forth and collate responsibly, mwah 💖✨🦋