doc/src/sgml/ref/alter_index.sgml

   1 <!--
   2 $PostgreSQL$
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="SQL-ALTERINDEX">
   7  <refmeta>
   8   <refentrytitle id="sql-alterindex-title">ALTER INDEX</refentrytitle>
   9   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  10  </refmeta>
  11
  12  <refnamediv>
  13   <refname>ALTER INDEX</refname>
  14   <refpurpose>change the definition of an index</refpurpose>
  15  </refnamediv>
  16
  17  <indexterm zone="sql-alterindex">
  18   <primary>ALTER INDEX</primary>
  19  </indexterm>
  20
  21  <refsynopsisdiv>
  22 <synopsis>
  23 ALTER INDEX <replaceable class="PARAMETER">name</replaceable> RENAME TO <replaceable class="PARAMETER">new_name</replaceable>
  24 ALTER INDEX <replaceable class="PARAMETER">name</replaceable> SET TABLESPACE <replaceable class="PARAMETER">tablespace_name</replaceable>
  25 ALTER INDEX <replaceable class="PARAMETER">name</replaceable> SET ( <replaceable class="PARAMETER">storage_parameter</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] )
  26 ALTER INDEX <replaceable class="PARAMETER">name</replaceable> RESET ( <replaceable class="PARAMETER">storage_parameter</replaceable> [, ... ] )
  27 </synopsis>
  28  </refsynopsisdiv>
  29
  30  <refsect1>
  31   <title>Description</title>
  32
  33   <para>
  34    <command>ALTER INDEX</command> changes the definition of an existing index.
  35    There are several subforms:
  36
  37   <variablelist>
  38
  39    <varlistentry>
  40     <term><literal>RENAME</literal></term>
  41     <listitem>
  42      <para>
  43       The <literal>RENAME</literal> form changes the name of the index.
  44       There is no effect on the stored data.
  45      </para>
  46     </listitem>
  47    </varlistentry>
  48
  49    <varlistentry>
  50     <term><literal>SET TABLESPACE</literal></term>
  51     <listitem>
  52      <para>
  53       This form changes the index's tablespace to the specified tablespace and
  54       moves the data file(s) associated with the index to the new tablespace.
  55       See also
  56       <xref linkend="SQL-CREATETABLESPACE" endterm="sql-createtablespace-title">.
  57      </para>
  58     </listitem>
  59    </varlistentry>
  60
  61    <varlistentry>
  62     <term><literal>SET ( <replaceable class="PARAMETER">storage_parameter</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] )</literal></term>
  63     <listitem>
  64      <para>
  65       This form changes one or more index-method-specific storage parameters
  66       for the index.  See
  67       <xref linkend="SQL-CREATEINDEX" endterm="sql-createindex-title">
  68       for details on the available parameters.  Note that the index contents
  69       will not be modified immediately by this command; depending on the
  70       parameter you might need to rebuild the index with
  71       <xref linkend="SQL-REINDEX" endterm="sql-reindex-title">
  72       to get the desired effects.
  73      </para>
  74     </listitem>
  75    </varlistentry>
  76
  77    <varlistentry>
  78     <term><literal>RESET ( <replaceable class="PARAMETER">storage_parameter</replaceable> [, ... ] )</literal></term>
  79     <listitem>
  80      <para>
  81       This form resets one or more index-method-specific storage parameters to
  82       their defaults.  As with <literal>SET</>, a <literal>REINDEX</literal>
  83       might be needed to update the index entirely.
  84      </para>
  85     </listitem>
  86    </varlistentry>
  87
  88   </variablelist>
  89   </para>
  90
  91  </refsect1>
  92
  93  <refsect1>
  94   <title>Parameters</title>
  95
  96     <variablelist>
  97
  98      <varlistentry>
  99       <term><replaceable class="PARAMETER">name</replaceable></term>
 100       <listitem>
 101        <para>
 102         The name (possibly schema-qualified) of an existing index to
 103         alter.
 104        </para>
 105       </listitem>
 106      </varlistentry>
 107
 108      <varlistentry>
 109       <term><replaceable class="PARAMETER">new_name</replaceable></term>
 110       <listitem>
 111        <para>
 112         The new name for the index.
 113        </para>
 114       </listitem>
 115      </varlistentry>
 116
 117      <varlistentry>
 118       <term><replaceable class="PARAMETER">tablespace_name</replaceable></term>
 119       <listitem>
 120        <para>
 121         The tablespace to which the index will be moved.
 122        </para>
 123       </listitem>
 124      </varlistentry>
 125
 126      <varlistentry>
 127       <term><replaceable class="PARAMETER">storage_parameter</replaceable></term>
 128       <listitem>
 129        <para>
 130         The name of an index-method-specific storage parameter.
 131        </para>
 132       </listitem>
 133      </varlistentry>
 134
 135      <varlistentry>
 136       <term><replaceable class="PARAMETER">value</replaceable></term>
 137       <listitem>
 138        <para>
 139         The new value for an index-method-specific storage parameter.
 140         This might be a number or a word depending on the parameter.
 141        </para>
 142       </listitem>
 143      </varlistentry>
 144
 145     </variablelist>
 146  </refsect1>
 147
 148  <refsect1>
 149   <title>Notes</title>
 150
 151    <para>
 152     These operations are also possible using
 153     <xref linkend="SQL-ALTERTABLE" endterm="SQL-ALTERTABLE-TITLE">.
 154     <command>ALTER INDEX</> is in fact just an alias for the forms
 155     of <command>ALTER TABLE</> that apply to indexes.
 156    </para>
 157
 158    <para>
 159     There was formerly an <command>ALTER INDEX OWNER</> variant, but
 160     this is now ignored (with a warning).  An index cannot have an owner
 161     different from its table's owner.  Changing the table's owner
 162     automatically changes the index as well.
 163    </para>
 164
 165    <para>
 166     Changing any part of a system catalog index is not permitted.
 167    </para>
 168  </refsect1>
 169
 170  <refsect1>
 171   <title>Examples</title>
 172   <para>
 173    To rename an existing index:
 174 <programlisting>
 175 ALTER INDEX distributors RENAME TO suppliers;
 176 </programlisting>
 177   </para>
 178
 179   <para>
 180    To move an index to a different tablespace:
 181 <programlisting>
 182 ALTER INDEX distributors SET TABLESPACE fasttablespace;
 183 </programlisting>
 184   </para>
 185
 186   <para>
 187    To change an index's fill factor (assuming that the index method
 188    supports it):
 189 <programlisting>
 190 ALTER INDEX distributors SET (fillfactor = 75);
 191 REINDEX INDEX distributors;
 192 </programlisting>
 193   </para>
 194
 195  </refsect1>
 196
 197  <refsect1>
 198   <title>Compatibility</title>
 199
 200   <para>
 201    <command>ALTER INDEX</> is a <productname>PostgreSQL</productname>
 202    extension.
 203   </para>
 204  </refsect1>
 205
 206
 207  <refsect1>
 208   <title>See Also</title>
 209
 210   <simplelist type="inline">
 211    <member><xref linkend="sql-createindex" endterm="sql-createindex-title"></member>
 212    <member><xref linkend="sql-reindex" endterm="sql-reindex-title"></member>
 213   </simplelist>
 214  </refsect1>
 215 </refentry>