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
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
|
'\" t
.\" Title: ALTER COLLATION
.\" Author: The PostgreSQL Global Development Group
.\" Generator: DocBook XSL Stylesheets vsnapshot <http://docbook.sf.net/>
.\" Date: 2023
.\" Manual: PostgreSQL 15.4 Documentation
.\" Source: PostgreSQL 15.4
.\" Language: English
.\"
.TH "ALTER COLLATION" "7" "2023" "PostgreSQL 15.4" "PostgreSQL 15.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_ROLE | 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 a collation object is created, the provider\-specific version of the collation is recorded in the system catalog\&. 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 upgrading the operating system to a new major version or 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 collation 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, version information is recorded on systems using the GNU C library (most Linux systems), FreeBSD and Windows\&. When using collations provided by ICU, the version information is provided by the ICU library and is available on all platforms\&.
.if n \{\
.sp
.\}
.RS 4
.it 1 an-trap
.nr an-no-space-flag 1
.nr an-break-flag 1
.br
.ps +1
\fBNote\fR
.ps -1
.br
.PP
When using the GNU C library for collations, the C library\*(Aqs version is used as a proxy for the collation version\&. Many Linux distributions change collation definitions only when upgrading the C library, but this approach is imperfect as maintainers are free to back\-port newer collation definitions to older C library releases\&.
.PP
When using Windows for collations, version information is only available for collations defined with BCP 47 language tags such as
en\-US\&.
.sp .5v
.RE
.PP
For the database default collation, there is an analogous command
ALTER DATABASE \&.\&.\&. REFRESH COLLATION VERSION\&.
.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))
|
