postgresql16/CREATE_COLLATION(7)

1CREATE COLLATION(7)      PostgreSQL 16.1 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           [ DETERMINISTIC = boolean, ]
15           [ RULES = rules, ]
16           [ VERSION = version ]
17       )
18       CREATE COLLATION [ IF NOT EXISTS ] name FROM existing_collation
19

DESCRIPTION

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

PARAMETERS

28       IF NOT EXISTS
29           Do not throw an error if a collation with the same name already
30           exists. A notice is issued in this case. Note that there is no
31           guarantee that the existing collation is anything like the one that
32           would have been created.
33
34       name
35           The name of the collation. The collation name can be
36           schema-qualified. If it is not, the collation is defined in the
37           current schema. The collation name must be unique within that
38           schema. (The system catalogs can contain collations with the same
39           name for other encodings, but these are ignored if the database
40           encoding does not match.)
41
42       locale
43           The locale name for this collation. See Section 24.2.2.3.1 and
44           Section 24.2.2.3.2 for details.
45
46           If provider is libc, this is a shortcut for setting LC_COLLATE and
47           LC_CTYPE at once. If you specify locale, you cannot specify either
48           of those parameters.
49
50       lc_collate
51           If provider is libc, use the specified operating system locale for
52           the LC_COLLATE locale category.
53
54       lc_ctype
55           If provider is libc, use the specified operating system locale for
56           the LC_CTYPE locale category.
57
58       provider
59           Specifies the provider to use for locale services associated with
60           this collation. Possible values are icu (if the server was built
61           with ICU support) or libc.  libc is the default. See Section 24.1.4
62           for details.
63
64       DETERMINISTIC
65           Specifies whether the collation should use deterministic
66           comparisons. The default is true. A deterministic comparison
67           considers strings that are not byte-wise equal to be unequal even
68           if they are considered logically equal by the comparison.
69           PostgreSQL breaks ties using a byte-wise comparison. Comparison
70           that is not deterministic can make the collation be, say, case- or
71           accent-insensitive. For that, you need to choose an appropriate
72           LC_COLLATE setting and set the collation to not deterministic here.
73
74           Nondeterministic collations are only supported with the ICU
75           provider.
76
77       rules
78           Specifies additional collation rules to customize the behavior of
79           the collation. This is supported for ICU only. See Section 24.2.3.4
80           for details.
81
82       version
83           Specifies the version string to store with the collation. Normally,
84           this should be omitted, which will cause the version to be computed
85           from the actual version of the collation as provided by the
86           operating system. This option is intended to be used by pg_upgrade
87           for copying the version from an existing installation.
88
89           See also ALTER COLLATION (ALTER_COLLATION(7)) for how to handle
90           collation version mismatches.
91
92       existing_collation
93           The name of an existing collation to copy. The new collation will
94           have the same properties as the existing one, but it will be an
95           independent object.
96

NOTES

98       CREATE COLLATION takes a SHARE ROW EXCLUSIVE lock, which is
99       self-conflicting, on the pg_collation system catalog, so only one
100       CREATE COLLATION command can run at a time.
101
102       Use DROP COLLATION to remove user-defined collations.
103
104       See Section 24.2.2.3 for more information on how to create collations.
105
106       When using the libc collation provider, the locale must be applicable
107       to the current database encoding. See CREATE DATABASE
108       (CREATE_DATABASE(7)) for the precise rules.
109

EXAMPLES

111       To create a collation from the operating system locale fr_FR.utf8
112       (assuming the current database encoding is UTF8):
113
114           CREATE COLLATION french (locale = 'fr_FR.utf8');
115
116       To create a collation using the ICU provider using German phone book
117       sort order:
118
119           CREATE COLLATION german_phonebook (provider = icu, locale = 'de-u-co-phonebk');
120
121       To create a collation using the ICU provider, based on the root ICU
122       locale, with custom rules:
123
124           CREATE COLLATION custom (provider = icu, locale = 'und', rules = '&V << w <<< W');
125
126       See Section 24.2.3.4 for further details and examples on the rules
127       syntax.
128
129       To create a collation from an existing collation:
130
131           CREATE COLLATION german FROM "de_DE";
132
133       This can be convenient to be able to use operating-system-independent
134       collation names in applications.
135

COMPATIBILITY

137       There is a CREATE COLLATION statement in the SQL standard, but it is
138       limited to copying an existing collation. The syntax to create a new
139       collation is a PostgreSQL extension.
140