3
PostgreSQL documentation
6
<refentry id="SQL-ALTERINDEX">
8
<refentrytitle id="sql-alterindex-title">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" endterm="sql-createtablespace-title">.
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" endterm="sql-createindex-title">
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" endterm="sql-reindex-title">
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" endterm="SQL-ALTERTABLE-TITLE">.
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;
199
<title>Compatibility</title>
202
<command>ALTER INDEX</> is a <productname>PostgreSQL</productname>
209
<title>See Also</title>
211
<simplelist type="inline">
212
<member><xref linkend="sql-createindex" endterm="sql-createindex-title"></member>
213
<member><xref linkend="sql-reindex" endterm="sql-reindex-title"></member>