2 doc/src/sgml/ref/alter_index.sgml
3 PostgreSQL documentation
6 <refentry id="SQL-ALTERINDEX">
8 <refentrytitle>ALTER INDEX</refentrytitle>
9 <manvolnum>7</manvolnum>
10 <refmiscinfo>SQL - Language Statements</refmiscinfo>
14 <refname>ALTER INDEX</refname>
15 <refpurpose>change the definition of an index</refpurpose>
18 <indexterm zone="sql-alterindex">
19 <primary>ALTER INDEX</primary>
24 ALTER INDEX <replaceable class="PARAMETER">name</replaceable> RENAME TO <replaceable class="PARAMETER">new_name</replaceable>
25 ALTER INDEX <replaceable class="PARAMETER">name</replaceable> SET TABLESPACE <replaceable class="PARAMETER">tablespace_name</replaceable>
26 ALTER INDEX <replaceable class="PARAMETER">name</replaceable> SET ( <replaceable class="PARAMETER">storage_parameter</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] )
27 ALTER INDEX <replaceable class="PARAMETER">name</replaceable> RESET ( <replaceable class="PARAMETER">storage_parameter</replaceable> [, ... ] )
32 <title>Description</title>
35 <command>ALTER INDEX</command> changes the definition of an existing index.
36 There are several subforms:
41 <term><literal>RENAME</literal></term>
44 The <literal>RENAME</literal> form changes the name of the index.
45 There is no effect on the stored data.
51 <term><literal>SET TABLESPACE</literal></term>
54 This form changes the index's tablespace to the specified tablespace and
55 moves the data file(s) associated with the index to the new tablespace.
57 <xref linkend="SQL-CREATETABLESPACE">.
63 <term><literal>SET ( <replaceable class="PARAMETER">storage_parameter</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] )</literal></term>
66 This form changes one or more index-method-specific storage parameters
68 <xref linkend="SQL-CREATEINDEX">
69 for details on the available parameters. Note that the index contents
70 will not be modified immediately by this command; depending on the
71 parameter you might need to rebuild the index with
72 <xref linkend="SQL-REINDEX">
73 to get the desired effects.
79 <term><literal>RESET ( <replaceable class="PARAMETER">storage_parameter</replaceable> [, ... ] )</literal></term>
82 This form resets one or more index-method-specific storage parameters to
83 their defaults. As with <literal>SET</>, a <literal>REINDEX</literal>
84 might be needed to update the index entirely.
95 <title>Parameters</title>
100 <term><replaceable class="PARAMETER">name</replaceable></term>
103 The name (possibly schema-qualified) of an existing index to
110 <term><replaceable class="PARAMETER">new_name</replaceable></term>
113 The new name for the index.
119 <term><replaceable class="PARAMETER">tablespace_name</replaceable></term>
122 The tablespace to which the index will be moved.
128 <term><replaceable class="PARAMETER">storage_parameter</replaceable></term>
131 The name of an index-method-specific storage parameter.
137 <term><replaceable class="PARAMETER">value</replaceable></term>
140 The new value for an index-method-specific storage parameter.
141 This might be a number or a word depending on the parameter.
153 These operations are also possible using
154 <xref linkend="SQL-ALTERTABLE">.
155 <command>ALTER INDEX</> is in fact just an alias for the forms
156 of <command>ALTER TABLE</> that apply to indexes.
160 There was formerly an <command>ALTER INDEX OWNER</> variant, but
161 this is now ignored (with a warning). An index cannot have an owner
162 different from its table's owner. Changing the table's owner
163 automatically changes the index as well.
167 Changing any part of a system catalog index is not permitted.
172 <title>Examples</title>
174 To rename an existing index:
176 ALTER INDEX distributors RENAME TO suppliers;
181 To move an index to a different tablespace:
183 ALTER INDEX distributors SET TABLESPACE fasttablespace;
188 To change an index's fill factor (assuming that the index method
191 ALTER INDEX distributors SET (fillfactor = 75);
192 REINDEX INDEX distributors;
193 </programlisting></para>
198 <title>Compatibility</title>
201 <command>ALTER INDEX</> is a <productname>PostgreSQL</productname>
208 <title>See Also</title>
210 <simplelist type="inline">
211 <member><xref linkend="sql-createindex"></member>
212 <member><xref linkend="sql-reindex"></member>