1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
|
'\" t
.\" Title: ALTER COLLATION
.\" Author: The PostgreSQL Global Development Group
.\" Generator: DocBook XSL Stylesheets v1.79.1 <http://docbook.sf.net/>
.\" Date: 2021
.\" Manual: PostgreSQL 13.4 Documentation
.\" Source: PostgreSQL 13.4
.\" Language: English
.\"
.TH "ALTER COLLATION" "7" "2021" "PostgreSQL 13.4" "PostgreSQL 13.4 Documentation"
.\" -----------------------------------------------------------------
.\" * Define some portability stuff
.\" -----------------------------------------------------------------
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.\" http://bugs.debian.org/507673
.\" http://lists.gnu.org/archive/html/groff/2009-02/msg00013.html
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.ie \n(.g .ds Aq \(aq
.el .ds Aq '
.\" -----------------------------------------------------------------
.\" * set default formatting
.\" -----------------------------------------------------------------
.\" disable hyphenation
.nh
.\" disable justification (adjust text to left margin only)
.ad l
.\" -----------------------------------------------------------------
.\" * MAIN CONTENT STARTS HERE *
.\" -----------------------------------------------------------------
.SH "NAME"
ALTER_COLLATION \- change the definition of a collation
.SH "SYNOPSIS"
.sp
.nf
ALTER COLLATION \fIname\fR REFRESH VERSION
ALTER COLLATION \fIname\fR RENAME TO \fInew_name\fR
ALTER COLLATION \fIname\fR OWNER TO { \fInew_owner\fR | CURRENT_USER | SESSION_USER }
ALTER COLLATION \fIname\fR SET SCHEMA \fInew_schema\fR
.fi
.SH "DESCRIPTION"
.PP
\fBALTER COLLATION\fR
changes the definition of a collation\&.
.PP
You must own the collation to use
\fBALTER COLLATION\fR\&. To alter the owner, you must also be a direct or indirect member of the new owning role, and that role must have
CREATE
privilege on the collation\*(Aqs schema\&. (These restrictions enforce that altering the owner doesn\*(Aqt do anything you couldn\*(Aqt do by dropping and recreating the collation\&. However, a superuser can alter ownership of any collation anyway\&.)
.SH "PARAMETERS"
.PP
\fIname\fR
.RS 4
The name (optionally schema\-qualified) of an existing collation\&.
.RE
.PP
\fInew_name\fR
.RS 4
The new name of the collation\&.
.RE
.PP
\fInew_owner\fR
.RS 4
The new owner of the collation\&.
.RE
.PP
\fInew_schema\fR
.RS 4
The new schema for the collation\&.
.RE
.PP
REFRESH VERSION
.RS 4
Update the collation\*(Aqs version\&. See
Notes
below\&.
.RE
.SH "NOTES"
.PP
When using collations provided by the ICU library, the ICU\-specific version of the collator is recorded in the system catalog when the collation object is created\&. When the collation is used, the current version is checked against the recorded version, and a warning is issued when there is a mismatch, for example:
.sp
.if n \{\
.RS 4
.\}
.nf
WARNING: collation "xx\-x\-icu" has version mismatch
DETAIL: The collation in the database was created using version 1\&.2\&.3\&.4, but the operating system provides version 2\&.3\&.4\&.5\&.
HINT: Rebuild all objects affected by this collation and run ALTER COLLATION pg_catalog\&."xx\-x\-icu" REFRESH VERSION, or build PostgreSQL with the right library version\&.
.fi
.if n \{\
.RE
.\}
.sp
A change in collation definitions can lead to corrupt indexes and other problems because the database system relies on stored objects having a certain sort order\&. Generally, this should be avoided, but it can happen in legitimate circumstances, such as when using
\fBpg_upgrade\fR
to upgrade to server binaries linked with a newer version of ICU\&. When this happens, all objects depending on the collation should be rebuilt, for example, using
\fBREINDEX\fR\&. When that is done, the collation version can be refreshed using the command
ALTER COLLATION \&.\&.\&. REFRESH VERSION\&. This will update the system catalog to record the current collator version and will make the warning go away\&. Note that this does not actually check whether all affected objects have been rebuilt correctly\&.
.PP
When using collations provided by
libc
and
PostgreSQL
was built with the GNU C library, the C library\*(Aqs version is used as a collation version\&. Since collation definitions typically change only with GNU C library releases, this provides some defense against corruption, but it is not completely reliable\&.
.PP
Currently, there is no version tracking for the database default collation\&.
.PP
The following query can be used to identify all collations in the current database that need to be refreshed and the objects that depend on them:
.sp
.if n \{\
.RS 4
.\}
.nf
SELECT pg_describe_object(refclassid, refobjid, refobjsubid) AS "Collation",
pg_describe_object(classid, objid, objsubid) AS "Object"
FROM pg_depend d JOIN pg_collation c
ON refclassid = \*(Aqpg_collation\*(Aq::regclass AND refobjid = c\&.oid
WHERE c\&.collversion <> pg_collation_actual_version(c\&.oid)
ORDER BY 1, 2;
.fi
.if n \{\
.RE
.\}
.SH "EXAMPLES"
.PP
To rename the collation
de_DE
to
german:
.sp
.if n \{\
.RS 4
.\}
.nf
ALTER COLLATION "de_DE" RENAME TO german;
.fi
.if n \{\
.RE
.\}
.PP
To change the owner of the collation
en_US
to
joe:
.sp
.if n \{\
.RS 4
.\}
.nf
ALTER COLLATION "en_US" OWNER TO joe;
.fi
.if n \{\
.RE
.\}
.SH "COMPATIBILITY"
.PP
There is no
\fBALTER COLLATION\fR
statement in the SQL standard\&.
.SH "SEE ALSO"
CREATE COLLATION (\fBCREATE_COLLATION\fR(7)), DROP COLLATION (\fBDROP_COLLATION\fR(7))
|