CREATE COLLATION
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, ]
[ VERSION = version ]
)
CREATE COLLATION [ IF NOT EXISTS ] name FROM existing_collation
Description
CREATE COLLATION defines a new collation using the specified operating system locale settings or by copying an existing collation.
To create a collation, you must have the CREATE privilege on the target schema.
Parameters
IF NOT EXISTS
Do not throw an error if a collation with the same name already exists. A notice is issued in this case. Note that there is no guarantee that the existing collation is similar to the one that would be created.
name
The name of the collation, which can be schema-qualified. If not schema-qualified, the collation is defined in the current schema. The collation name must be unique within its schema (the system catalog may contain collations with the same name for other encodings, but they are ignored when the database encoding does not match).
locale
This is a shortcut for setting both LC_COLLATE and LC_CTYPE at once. If you specify this, you cannot specify those two parameters.
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
Specifies the provider to use for locale services associated with this collation. Possible values are: icu, libc. The default is libc. Available choices depend on the operating system and build options.
DETERMINISTIC
Specifies whether the collation should use deterministic comparisons. The default is true. Deterministic comparisons consider strings that are not equal at the byte level to be unequal, even if they are considered logically equal by comparison. The system uses byte comparison to break ties. Non-deterministic comparisons can make a collation case-insensitive or accent-insensitive. Therefore, you need to choose an appropriate LC_COLLATE setting and set the collation to non-deterministic here. Non-deterministic collations are only supported by the ICU provider.
version
Specifies the version string stored with the collation. This option is usually omitted, which causes the version to be computed from the actual version of the collation provided by the operating system. This option is intended for use by pg_upgrade to replicate versions from existing installations.
See also ALTER COLLATION for how to handle collation version mismatches.
existing_collation
The name of an existing collation to copy. The new collation will have the same properties as the existing one, but it is an independent object.
Notes
CREATE COLLATION takes a SHARE ROW EXCLUSIVE lock on the pg_collation system catalog, which is self-conflicting, so only one CREATE COLLATION command can run at a time.
Use DROP COLLATION to remove user-defined collations.
When using the libc collation provider, the locale must be appropriate for the current database encoding. See CREATE DATABASE for the exact rules.
Examples
# 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');
# Create a collation using the German phone book sort order with the ICU provider:
CREATE COLLATION german_phonebook (provider = icu, locale = 'de-u-co-phonebk');
# Create a new collation from an existing collation:
CREATE COLLATION german FROM "de_DE";
# It can be convenient to use operating-system-independent collation names in applications.