Debian Codegraph @

doc/src/sgml/ref/alter_index.sgml

raw
1 <!--
2 doc/src/sgml/ref/alter_index.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="sql-alterindex">
7 <indexterm zone="sql-alterindex">
8 <primary>ALTER INDEX</primary>
9 </indexterm>
10
11 <refmeta>
12 <refentrytitle>ALTER INDEX</refentrytitle>
13 <manvolnum>7</manvolnum>
14 <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18 <refname>ALTER INDEX</refname>
19 <refpurpose>change the definition of an index</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23 <synopsis>
24 ALTER INDEX [ IF EXISTS ] <replaceable class="parameter">name</replaceable> RENAME TO <replaceable class="parameter">new_name</replaceable>
25 ALTER INDEX [ IF EXISTS ] <replaceable class="parameter">name</replaceable> SET TABLESPACE <replaceable class="parameter">tablespace_name</replaceable>
26 ALTER INDEX <replaceable class="parameter">name</replaceable> ATTACH PARTITION <replaceable class="parameter">index_name</replaceable>
27 ALTER INDEX <replaceable class="parameter">name</replaceable> [ NO ] DEPENDS ON EXTENSION <replaceable class="parameter">extension_name</replaceable>
28 ALTER INDEX [ IF EXISTS ] <replaceable class="parameter">name</replaceable> SET ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )
29 ALTER INDEX [ IF EXISTS ] <replaceable class="parameter">name</replaceable> RESET ( <replaceable class="parameter">storage_parameter</replaceable> [, ... ] )
30 ALTER INDEX [ IF EXISTS ] <replaceable class="parameter">name</replaceable> ALTER [ COLUMN ] <replaceable class="parameter">column_number</replaceable>
31 SET STATISTICS <replaceable class="parameter">integer</replaceable>
32 ALTER INDEX ALL IN TABLESPACE <replaceable class="parameter">name</replaceable> [ OWNED BY <replaceable class="parameter">role_name</replaceable> [, ... ] ]
33 SET TABLESPACE <replaceable class="parameter">new_tablespace</replaceable> [ NOWAIT ]
34 </synopsis>
35 </refsynopsisdiv>
36
37 <refsect1>
38 <title>Description</title>
39
40 <para>
41 <command>ALTER INDEX</command> changes the definition of an existing index.
42 There are several subforms described below. Note that the lock level required
43 may differ for each subform. An <literal>ACCESS EXCLUSIVE</literal> lock is held
44 unless explicitly noted. When multiple subcommands are listed, the lock
45 held will be the strictest one required from any subcommand.
46
47 <variablelist>
48
49 <varlistentry>
50 <term><literal>RENAME</literal></term>
51 <listitem>
52 <para>
53 The <literal>RENAME</literal> form changes the name of the index.
54 If the index is associated with a table constraint (either
55 <literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>,
56 or <literal>EXCLUDE</literal>), the constraint is renamed as well.
57 There is no effect on the stored data.
58 </para>
59 <para>
60 Renaming an index acquires a <literal>SHARE UPDATE EXCLUSIVE</literal>
61 lock.
62 </para>
63 </listitem>
64 </varlistentry>
65
66 <varlistentry>
67 <term><literal>SET TABLESPACE</literal></term>
68 <listitem>
69 <para>
70 This form changes the index's tablespace to the specified tablespace and
71 moves the data file(s) associated with the index to the new tablespace.
72 To change the tablespace of an index, you must own the index and have
73 <literal>CREATE</literal> privilege on the new tablespace.
74 All indexes in the current database in a tablespace can be moved by using
75 the <literal>ALL IN TABLESPACE</literal> form, which will lock all
76 indexes to be moved and then move each one. This form also supports
77 <literal>OWNED BY</literal>, which will only move indexes owned by the
78 roles specified. If the <literal>NOWAIT</literal> option is specified
79 then the command will fail if it is unable to acquire all of the locks
80 required immediately. Note that system catalogs will not be moved by
81 this command, use <command>ALTER DATABASE</command> or explicit
82 <command>ALTER INDEX</command> invocations instead if desired.
83 See also
84 <link linkend="sql-createtablespace"><command>CREATE TABLESPACE</command></link>.
85 </para>
86 </listitem>
87 </varlistentry>
88
89 <varlistentry>
90 <term><literal>ATTACH PARTITION <replaceable class="parameter">index_name</replaceable></literal></term>
91 <listitem>
92 <para>
93 Causes the named index (possibly schema-qualified) to become attached
94 to the altered index.
95 The named index must be on a partition of the table containing the
96 index being altered, and have an equivalent definition. An attached
97 index cannot be dropped by itself, and will automatically be dropped
98 if its parent index is dropped.
99 </para>
100 <para>
101 If the named index is already attached to the altered index, the
102 command will attempt to validate the parent index if the parent is
103 currently invalid.
104 </para>
105 </listitem>
106 </varlistentry>
107
108 <varlistentry>
109 <term><literal>DEPENDS ON EXTENSION <replaceable class="parameter">extension_name</replaceable></literal></term>
110 <term><literal>NO DEPENDS ON EXTENSION <replaceable class="parameter">extension_name</replaceable></literal></term>
111 <listitem>
112 <para>
113 This form marks the index as dependent on the extension, or no longer
114 dependent on that extension if <literal>NO</literal> is specified.
115 An index that's marked as dependent on an extension is automatically
116 dropped when the extension is dropped.
117 </para>
118 </listitem>
119 </varlistentry>
120
121 <varlistentry>
122 <term><literal>SET ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term>
123 <listitem>
124 <para>
125 This form changes one or more index-method-specific storage parameters
126 for the index. See
127 <link linkend="sql-createindex"><command>CREATE INDEX</command></link>
128 for details on the available parameters. Note that the index contents
129 will not be modified immediately by this command; depending on the
130 parameter you might need to rebuild the index with
131 <link linkend="sql-reindex"><command>REINDEX</command></link>
132 to get the desired effects.
133 </para>
134 </listitem>
135 </varlistentry>
136
137 <varlistentry>
138 <term><literal>RESET ( <replaceable class="parameter">storage_parameter</replaceable> [, ... ] )</literal></term>
139 <listitem>
140 <para>
141 This form resets one or more index-method-specific storage parameters to
142 their defaults. As with <literal>SET</literal>, a <literal>REINDEX</literal>
143 might be needed to update the index entirely.
144 </para>
145 </listitem>
146 </varlistentry>
147
148 <varlistentry>
149 <term><literal>ALTER [ COLUMN ] <replaceable class="parameter">column_number</replaceable> SET STATISTICS <replaceable class="parameter">integer</replaceable></literal></term>
150 <listitem>
151 <para>
152 This form sets the per-column statistics-gathering target for
153 subsequent <link linkend="sql-analyze"><command>ANALYZE</command></link> operations, though can
154 be used only on index columns that are defined as an expression.
155 Since expressions lack a unique name, we refer to them using the
156 ordinal number of the index column.
157 The target can be set in the range 0 to 10000; alternatively, set it
158 to -1 to revert to using the system default statistics
159 target (<xref linkend="guc-default-statistics-target"/>).
160 For more information on the use of statistics by the
161 <productname>PostgreSQL</productname> query planner, refer to
162 <xref linkend="planner-stats"/>.
163 </para>
164 </listitem>
165 </varlistentry>
166
167 </variablelist>
168 </para>
169
170 </refsect1>
171
172 <refsect1>
173 <title>Parameters</title>
174
175 <variablelist>
176
177 <varlistentry>
178 <term><literal>IF EXISTS</literal></term>
179 <listitem>
180 <para>
181 Do not throw an error if the index does not exist. A notice is issued
182 in this case.
183 </para>
184 </listitem>
185 </varlistentry>
186
187 <varlistentry>
188 <term><replaceable class="parameter">column_number</replaceable></term>
189 <listitem>
190 <para>
191 The ordinal number refers to the ordinal (left-to-right) position
192 of the index column.
193 </para>
194 </listitem>
195 </varlistentry>
196
197 <varlistentry>
198 <term><replaceable class="parameter">name</replaceable></term>
199 <listitem>
200 <para>
201 The name (possibly schema-qualified) of an existing index to
202 alter.
203 </para>
204 </listitem>
205 </varlistentry>
206
207 <varlistentry>
208 <term><replaceable class="parameter">new_name</replaceable></term>
209 <listitem>
210 <para>
211 The new name for the index.
212 </para>
213 </listitem>
214 </varlistentry>
215
216 <varlistentry>
217 <term><replaceable class="parameter">tablespace_name</replaceable></term>
218 <listitem>
219 <para>
220 The tablespace to which the index will be moved.
221 </para>
222 </listitem>
223 </varlistentry>
224
225 <varlistentry>
226 <term><replaceable class="parameter">extension_name</replaceable></term>
227 <listitem>
228 <para>
229 The name of the extension that the index is to depend on.
230 </para>
231 </listitem>
232 </varlistentry>
233
234 <varlistentry>
235 <term><replaceable class="parameter">storage_parameter</replaceable></term>
236 <listitem>
237 <para>
238 The name of an index-method-specific storage parameter.
239 </para>
240 </listitem>
241 </varlistentry>
242
243 <varlistentry>
244 <term><replaceable class="parameter">value</replaceable></term>
245 <listitem>
246 <para>
247 The new value for an index-method-specific storage parameter.
248 This might be a number or a word depending on the parameter.
249 </para>
250 </listitem>
251 </varlistentry>
252
253 </variablelist>
254 </refsect1>
255
256 <refsect1>
257 <title>Notes</title>
258
259 <para>
260 These operations are also possible using
261 <link linkend="sql-altertable"><command>ALTER TABLE</command></link>.
262 <command>ALTER INDEX</command> is in fact just an alias for the forms
263 of <command>ALTER TABLE</command> that apply to indexes.
264 </para>
265
266 <para>
267 There was formerly an <command>ALTER INDEX OWNER</command> variant, but
268 this is now ignored (with a warning). An index cannot have an owner
269 different from its table's owner. Changing the table's owner
270 automatically changes the index as well.
271 </para>
272
273 <para>
274 Changing any part of a system catalog index is not permitted.
275 </para>
276 </refsect1>
277
278 <refsect1>
279 <title>Examples</title>
280 <para>
281 To rename an existing index:
282 <programlisting>
283 ALTER INDEX distributors RENAME TO suppliers;
284 </programlisting>
285 </para>
286
287 <para>
288 To move an index to a different tablespace:
289 <programlisting>
290 ALTER INDEX distributors SET TABLESPACE fasttablespace;
291 </programlisting>
292 </para>
293
294 <para>
295 To change an index's fill factor (assuming that the index method
296 supports it):
297 <programlisting>
298 ALTER INDEX distributors SET (fillfactor = 75);
299 REINDEX INDEX distributors;
300 </programlisting></para>
301
302 <para>
303 Set the statistics-gathering target for an expression index:
304 <programlisting>
305 CREATE INDEX coord_idx ON measured (x, y, (z + t));
306 ALTER INDEX coord_idx ALTER COLUMN 3 SET STATISTICS 1000;
307 </programlisting></para>
308
309 </refsect1>
310
311 <refsect1>
312 <title>Compatibility</title>
313
314 <para>
315 <command>ALTER INDEX</command> is a <productname>PostgreSQL</productname>
316 extension.
317 </para>
318 </refsect1>
319
320
321 <refsect1>
322 <title>See Also</title>
323
324 <simplelist type="inline">
325 <member><xref linkend="sql-createindex"/></member>
326 <member><xref linkend="sql-reindex"/></member>
327 </simplelist>
328 </refsect1>
329 </refentry>