create_collation(7)

NAME#

CREATE_COLLATION - define a new collation

SYNOPSIS#

CREATE COLLATION [ IF NOT EXISTS ] \nname\n (
\n
    [ LOCALE = \nlocale\n, ]
\n
    [ LC_COLLATE = \nlc_collate\n, ]
\n
    [ LC_CTYPE = \nlc_ctype\n, ]
\n
    [ PROVIDER = \nprovider\n, ]
\n
    [ DETERMINISTIC = \nboolean\n, ]
\n
    [ RULES = \nrules\n, ]
\n
    [ VERSION = \nversion\n ]
)
CREATE COLLATION [ IF NOT EXISTS ] \nname\n FROM \nexisting_collation

DESCRIPTION#

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.

PARAMETERS#

IF NOT EXISTS

name

locale

If provider is libc, this is a shortcut for setting LC_COLLATE and LC_CTYPE at once. If you specify locale, you cannot specify either of those parameters.

If provider is builtin, then locale must be specified and set to either C or C.UTF-8.

lc_collate

lc_ctype

provider

DETERMINISTIC

Nondeterministic collations are only supported with the ICU provider.

rules

version

See also ALTER COLLATION (ALTER_COLLATION(7)) for how to handle collation version mismatches.

existing_collation

NOTES#

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 23.2.2.3 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.

EXAMPLES#

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 using 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.

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.

COMPATIBILITY#

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.

SEE ALSO#

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