- bullseye 13.5-0+deb11u1
|CREATE COLLATION(7)||PostgreSQL 13.5 Documentation||CREATE COLLATION(7)|
CREATE_COLLATION - define a new collation
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
CREATE COLLATION defines a new collation using the specified operating system locale settings, or by copying an existing collation.
To be able to create a collation, you must have CREATE privilege on the destination schema.
IF NOT EXISTS
Nondeterministic collations are only supported with the ICU provider.
See also ALTER COLLATION (ALTER_COLLATION(7)) for how to handle collation version mismatches.
CREATE COLLATION takes a SHARE ROW EXCLUSIVE lock, which is self-conflicting, on the pg_collation system catalog, so only one CREATE COLLATION command can run at a time.
Use DROP COLLATION to remove user-defined collations.
See Section 18.104.22.168 for more information on how to create collations.
When 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.
To create a collation from the operating system locale fr_FR.utf8 (assuming the current database encoding is UTF8):
CREATE COLLATION french (locale = 'fr_FR.utf8');
To create a collation using the ICU provider using German phone book sort order:
CREATE COLLATION german_phonebook (provider = icu, locale = 'de-u-co-phonebk');
To create a collation from an existing collation:
CREATE COLLATION german FROM "de_DE";
This can be convenient to be able to use operating-system-independent collation names in applications.
There is a CREATE COLLATION statement in the SQL standard, but it is limited to copying an existing collation. The syntax to create a new collation is a PostgreSQL extension.