CREATE COLLATION(7) PostgreSQL 10.1 Documentation CREATE COLLATION(7)NAMECREATE_COLLATION - define a new collation
SYNOPSIS
CREATE COLLATION [ IF NOT EXISTS ] name (
[ LOCALE = locale, ]
[ LC_COLLATE = lc_collate, ]
[ LC_CTYPE = lc_ctype, ]
[ PROVIDER = provider, ]
[ 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 be able to create a collation, you must have CREATE privilege on the
destination 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 anything like the one that
would have been created.
name
The name of the collation. The collation name can be
schema-qualified. If it is not, the collation is defined in the
current schema. The collation name must be unique within that
schema. (The system catalogs can contain collations with the same
name for other encodings, but these are ignored if the database
encoding does not match.)
locale
This is a shortcut for setting LC_COLLATE and LC_CTYPE at once. If
you specify this, you cannot specify either of those 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. libc is the
default. The available choices depend on the operating system and
build options.
version
Specifies the version string to store with the collation. Normally,
this should be omitted, which will cause the version to be computed
from the actual version of the collation as provided by the
operating system. This option is intended to be used by pg_upgrade
for copying the version from an existing installation.
See also ALTER COLLATION (ALTER_COLLATION(7)) 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 will be an
independent object.
NOTES
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 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))
PostgreSQL 10.1 2017 CREATE COLLATION(7)
