CREATE_COLLATION(7)

1CREATE COLLATION(7)      PostgreSQL 11.6 Documentation     CREATE COLLATION(7)
2
3
4

NAME

6       CREATE_COLLATION - define a new collation
7

SYNOPSIS

9       CREATE COLLATION [ IF NOT EXISTS ] name (
10           [ LOCALE = locale, ]
11           [ LC_COLLATE = lc_collate, ]
12           [ LC_CTYPE = lc_ctype, ]
13           [ PROVIDER = provider, ]
14           [ VERSION = version ]
15       )
16       CREATE COLLATION [ IF NOT EXISTS ] name FROM existing_collation
17

DESCRIPTION

19       CREATE COLLATION defines a new collation using the specified operating
20       system locale settings, or by copying an existing collation.
21
22       To be able to create a collation, you must have CREATE privilege on the
23       destination schema.
24

PARAMETERS

26       IF NOT EXISTS
27           Do not throw an error if a collation with the same name already
28           exists. A notice is issued in this case. Note that there is no
29           guarantee that the existing collation is anything like the one that
30           would have been created.
31
32       name
33           The name of the collation. The collation name can be
34           schema-qualified. If it is not, the collation is defined in the
35           current schema. The collation name must be unique within that
36           schema. (The system catalogs can contain collations with the same
37           name for other encodings, but these are ignored if the database
38           encoding does not match.)
39
40       locale
41           This is a shortcut for setting LC_COLLATE and LC_CTYPE at once. If
42           you specify this, you cannot specify either of those parameters.
43
44       lc_collate
45           Use the specified operating system locale for the LC_COLLATE locale
46           category.
47
48       lc_ctype
49           Use the specified operating system locale for the LC_CTYPE locale
50           category.
51
52       provider
53           Specifies the provider to use for locale services associated with
54           this collation. Possible values are: icu, libc.  libc is the
55           default. The available choices depend on the operating system and
56           build options.
57
58       version
59           Specifies the version string to store with the collation. Normally,
60           this should be omitted, which will cause the version to be computed
61           from the actual version of the collation as provided by the
62           operating system. This option is intended to be used by pg_upgrade
63           for copying the version from an existing installation.
64
65           See also ALTER COLLATION (ALTER_COLLATION(7)) for how to handle
66           collation version mismatches.
67
68       existing_collation
69           The name of an existing collation to copy. The new collation will
70           have the same properties as the existing one, but it will be an
71           independent object.
72

NOTES

74       Use DROP COLLATION to remove user-defined collations.
75
76       See Section 23.2.2.3 for more information on how to create collations.
77
78       When using the libc collation provider, the locale must be applicable
79       to the current database encoding. See CREATE DATABASE
80       (CREATE_DATABASE(7)) for the precise rules.
81

EXAMPLES

83       To create a collation from the operating system locale fr_FR.utf8
84       (assuming the current database encoding is UTF8):
85
86           CREATE COLLATION french (locale = 'fr_FR.utf8');
87
88       To create a collation using the ICU provider using German phone book
89       sort order:
90
91           CREATE COLLATION german_phonebook (provider = icu, locale = 'de-u-co-phonebk');
92
93       To create a collation from an existing collation:
94
95           CREATE COLLATION german FROM "de_DE";
96
97       This can be convenient to be able to use operating-system-independent
98       collation names in applications.
99

COMPATIBILITY

101       There is a CREATE COLLATION statement in the SQL standard, but it is
102       limited to copying an existing collation. The syntax to create a new
103       collation is a PostgreSQL extension.
104