2 doc/src/sgml/ref/alter_index.sgml
3 PostgreSQL documentation
6 <refentry id="SQL-ALTERINDEX">
7 <indexterm zone="sql-alterindex">
8 <primary>ALTER INDEX</primary>
12 <refentrytitle>ALTER INDEX</refentrytitle>
13 <manvolnum>7</manvolnum>
14 <refmiscinfo>SQL - Language Statements</refmiscinfo>
18 <refname>ALTER INDEX</refname>
19 <refpurpose>change the definition of an index</refpurpose>
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> DEPENDS ON EXTENSION <replaceable class="PARAMETER">extension_name</replaceable>
27 ALTER INDEX [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> SET ( <replaceable class="PARAMETER">storage_parameter</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] )
28 ALTER INDEX [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> RESET ( <replaceable class="PARAMETER">storage_parameter</replaceable> [, ... ] )
29 ALTER INDEX ALL IN TABLESPACE <replaceable class="PARAMETER">name</replaceable> [ OWNED BY <replaceable class="PARAMETER">role_name</replaceable> [, ... ] ]
30 SET TABLESPACE <replaceable class="PARAMETER">new_tablespace</replaceable> [ NOWAIT ]
35 <title>Description</title>
38 <command>ALTER INDEX</command> changes the definition of an existing index.
39 There are several subforms:
44 <term><literal>RENAME</literal></term>
47 The <literal>RENAME</literal> form changes the name of the index.
48 There is no effect on the stored data.
54 <term><literal>SET TABLESPACE</literal></term>
57 This form changes the index's tablespace to the specified tablespace and
58 moves the data file(s) associated with the index to the new tablespace.
59 To change the tablespace of an index, you must own the index and have
60 <literal>CREATE</literal> privilege on the new tablespace.
61 All indexes in the current database in a tablespace can be moved by using
62 the <literal>ALL IN TABLESPACE</literal> form, which will lock all
63 indexes to be moved and then move each one. This form also supports
64 <literal>OWNED BY</literal>, which will only move indexes owned by the
65 roles specified. If the <literal>NOWAIT</literal> option is specified
66 then the command will fail if it is unable to acquire all of the locks
67 required immediately. Note that system catalogs will not be moved by
68 this command, use <command>ALTER DATABASE</command> or explicit
69 <command>ALTER INDEX</command> invocations instead if desired.
71 <xref linkend="SQL-CREATETABLESPACE">.
77 <term><literal>DEPENDS ON EXTENSION</literal></term>
80 This form marks the index as dependent on the extension, such that if the
81 extension is dropped, the index will automatically be dropped as well.
87 <term><literal>SET ( <replaceable class="PARAMETER">storage_parameter</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] )</literal></term>
90 This form changes one or more index-method-specific storage parameters
92 <xref linkend="SQL-CREATEINDEX">
93 for details on the available parameters. Note that the index contents
94 will not be modified immediately by this command; depending on the
95 parameter you might need to rebuild the index with
96 <xref linkend="SQL-REINDEX">
97 to get the desired effects.
103 <term><literal>RESET ( <replaceable class="PARAMETER">storage_parameter</replaceable> [, ... ] )</literal></term>
106 This form resets one or more index-method-specific storage parameters to
107 their defaults. As with <literal>SET</>, a <literal>REINDEX</literal>
108 might be needed to update the index entirely.
119 <title>Parameters</title>
124 <term><literal>IF EXISTS</literal></term>
127 Do not throw an error if the index does not exist. A notice is issued
134 <term><replaceable class="PARAMETER">name</replaceable></term>
137 The name (possibly schema-qualified) of an existing index to
144 <term><replaceable class="PARAMETER">new_name</replaceable></term>
147 The new name for the index.
153 <term><replaceable class="PARAMETER">tablespace_name</replaceable></term>
156 The tablespace to which the index will be moved.
162 <term><replaceable class="PARAMETER">extension_name</replaceable></term>
165 The name of the extension that the index is to depend on.
171 <term><replaceable class="PARAMETER">storage_parameter</replaceable></term>
174 The name of an index-method-specific storage parameter.
180 <term><replaceable class="PARAMETER">value</replaceable></term>
183 The new value for an index-method-specific storage parameter.
184 This might be a number or a word depending on the parameter.
196 These operations are also possible using
197 <xref linkend="SQL-ALTERTABLE">.
198 <command>ALTER INDEX</> is in fact just an alias for the forms
199 of <command>ALTER TABLE</> that apply to indexes.
203 There was formerly an <command>ALTER INDEX OWNER</> variant, but
204 this is now ignored (with a warning). An index cannot have an owner
205 different from its table's owner. Changing the table's owner
206 automatically changes the index as well.
210 Changing any part of a system catalog index is not permitted.
215 <title>Examples</title>
217 To rename an existing index:
219 ALTER INDEX distributors RENAME TO suppliers;
224 To move an index to a different tablespace:
226 ALTER INDEX distributors SET TABLESPACE fasttablespace;
231 To change an index's fill factor (assuming that the index method
234 ALTER INDEX distributors SET (fillfactor = 75);
235 REINDEX INDEX distributors;
236 </programlisting></para>
241 <title>Compatibility</title>
244 <command>ALTER INDEX</> is a <productname>PostgreSQL</productname>
251 <title>See Also</title>
253 <simplelist type="inline">
254 <member><xref linkend="sql-createindex"></member>
255 <member><xref linkend="sql-reindex"></member>