| <!-- |
| $PostgreSQL: pgsql/doc/src/sgml/ref/alter_index.sgml,v 1.14 2008/11/14 10:22:45 petere Exp $ |
| PostgreSQL documentation |
| --> |
| |
| <refentry id="SQL-ALTERINDEX"> |
| <refmeta> |
| <refentrytitle id="sql-alterindex-title">ALTER INDEX</refentrytitle> |
| <manvolnum>7</manvolnum> |
| <refmiscinfo>SQL - Language Statements</refmiscinfo> |
| </refmeta> |
| |
| <refnamediv> |
| <refname>ALTER INDEX</refname> |
| <refpurpose>change the definition of an index</refpurpose> |
| </refnamediv> |
| |
| <indexterm zone="sql-alterindex"> |
| <primary>ALTER INDEX</primary> |
| </indexterm> |
| |
| <refsynopsisdiv> |
| <synopsis> |
| ALTER INDEX <replaceable class="PARAMETER">name</replaceable> RENAME TO <replaceable class="PARAMETER">new_name</replaceable> |
| ALTER INDEX <replaceable class="PARAMETER">name</replaceable> SET TABLESPACE <replaceable class="PARAMETER">tablespace_name</replaceable> |
| ALTER INDEX <replaceable class="PARAMETER">name</replaceable> SET ( <replaceable class="PARAMETER">storage_parameter</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] ) |
| ALTER INDEX <replaceable class="PARAMETER">name</replaceable> RESET ( <replaceable class="PARAMETER">storage_parameter</replaceable> [, ... ] ) |
| </synopsis> |
| </refsynopsisdiv> |
| |
| <refsect1> |
| <title>Description</title> |
| |
| <para> |
| <command>ALTER INDEX</command> changes the definition of an existing index. |
| There are several subforms: |
| |
| <variablelist> |
| |
| <varlistentry> |
| <term><literal>RENAME</literal></term> |
| <listitem> |
| <para> |
| The <literal>RENAME</literal> form changes the name of the index. |
| There is no effect on the stored data. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>SET TABLESPACE</literal></term> |
| <listitem> |
| <para> |
| This form changes the index's tablespace to the specified tablespace and |
| moves the data file(s) associated with the index to the new tablespace. |
| See also |
| <xref linkend="SQL-CREATETABLESPACE" endterm="sql-createtablespace-title">. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>SET ( <replaceable class="PARAMETER">storage_parameter</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] )</literal></term> |
| <listitem> |
| <para> |
| This form changes one or more index-method-specific storage parameters |
| for the index. See |
| <xref linkend="SQL-CREATEINDEX" endterm="sql-createindex-title"> |
| for details on the available parameters. Note that the index contents |
| will not be modified immediately by this command; depending on the |
| parameter you might need to rebuild the index with |
| <xref linkend="SQL-REINDEX" endterm="sql-reindex-title"> |
| to get the desired effects. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>RESET ( <replaceable class="PARAMETER">storage_parameter</replaceable> [, ... ] )</literal></term> |
| <listitem> |
| <para> |
| This form resets one or more index-method-specific storage parameters to |
| their defaults. As with <literal>SET</>, a <literal>REINDEX</literal> |
| might be needed to update the index entirely. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| </variablelist> |
| </para> |
| |
| </refsect1> |
| |
| <refsect1> |
| <title>Parameters</title> |
| |
| <variablelist> |
| |
| <varlistentry> |
| <term><replaceable class="PARAMETER">name</replaceable></term> |
| <listitem> |
| <para> |
| The name (possibly schema-qualified) of an existing index to |
| alter. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="PARAMETER">new_name</replaceable></term> |
| <listitem> |
| <para> |
| The new name for the index. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="PARAMETER">tablespace_name</replaceable></term> |
| <listitem> |
| <para> |
| The tablespace to which the index will be moved. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="PARAMETER">storage_parameter</replaceable></term> |
| <listitem> |
| <para> |
| The name of an index-method-specific storage parameter. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="PARAMETER">value</replaceable></term> |
| <listitem> |
| <para> |
| The new value for an index-method-specific storage parameter. |
| This might be a number or a word depending on the parameter. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| </variablelist> |
| </refsect1> |
| |
| <refsect1> |
| <title>Notes</title> |
| |
| <para> |
| These operations are also possible using |
| <xref linkend="SQL-ALTERTABLE" endterm="SQL-ALTERTABLE-TITLE">. |
| <command>ALTER INDEX</> is in fact just an alias for the forms |
| of <command>ALTER TABLE</> that apply to indexes. |
| </para> |
| |
| <para> |
| There was formerly an <command>ALTER INDEX OWNER</> variant, but |
| this is now ignored (with a warning). An index cannot have an owner |
| different from its table's owner. Changing the table's owner |
| automatically changes the index as well. |
| </para> |
| |
| <para> |
| Changing any part of a system catalog index is not permitted. |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>Examples</title> |
| <para> |
| To rename an existing index: |
| <programlisting> |
| ALTER INDEX distributors RENAME TO suppliers; |
| </programlisting> |
| </para> |
| |
| <para> |
| To move an index to a different tablespace: |
| <programlisting> |
| ALTER INDEX distributors SET TABLESPACE fasttablespace; |
| </programlisting> |
| </para> |
| |
| <para> |
| To change an index's fill factor (assuming that the index method |
| supports it): |
| <programlisting> |
| ALTER INDEX distributors SET (fillfactor = 75); |
| REINDEX INDEX distributors; |
| </programlisting> |
| </para> |
| |
| </refsect1> |
| |
| <refsect1> |
| <title>Compatibility</title> |
| |
| <para> |
| <command>ALTER INDEX</> is a <productname>PostgreSQL</productname> |
| extension. |
| </para> |
| </refsect1> |
| |
| |
| <refsect1> |
| <title>See Also</title> |
| |
| <simplelist type="inline"> |
| <member><xref linkend="sql-createindex" endterm="sql-createindex-title"></member> |
| <member><xref linkend="sql-reindex" endterm="sql-reindex-title"></member> |
| </simplelist> |
| </refsect1> |
| </refentry> |