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   <manvolnum>7</manvolnum>
  10   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  11  </refmeta>
  12
  13  <refnamediv>
  14   <refname>ALTER INDEX</refname>
  15   <refpurpose>change the definition of an index</refpurpose>
  16  </refnamediv>
  17
  18  <indexterm zone="sql-alterindex">
  19   <primary>ALTER INDEX</primary>
  20  </indexterm>
  21
  22  <refsynopsisdiv>
  23 <synopsis>
  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> [, ... ] )
  28 </synopsis>
  29  </refsynopsisdiv>
  30
  31  <refsect1>
  32   <title>Description</title>
  33
  34   <para>
  35    <command>ALTER INDEX</command> changes the definition of an existing index.
  36    There are several subforms:
  37
  38   <variablelist>
  39
  40    <varlistentry>
  41     <term><literal>RENAME</literal></term>
  42     <listitem>
  43      <para>
  44       The <literal>RENAME</literal> form changes the name of the index.
  45       There is no effect on the stored data.
  46      </para>
  47     </listitem>
  48    </varlistentry>
  49
  50    <varlistentry>
  51     <term><literal>SET TABLESPACE</literal></term>
  52     <listitem>
  53      <para>
  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.
  56       See also
  57       <xref linkend="SQL-CREATETABLESPACE" endterm="sql-createtablespace-title">.
  58      </para>
  59     </listitem>
  60    </varlistentry>
  61
  62    <varlistentry>
  63     <term><literal>SET ( <replaceable class="PARAMETER">storage_parameter</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] )</literal></term>
  64     <listitem>
  65      <para>
  66       This form changes one or more index-method-specific storage parameters
  67       for the index.  See
  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.
  74      </para>
  75     </listitem>
  76    </varlistentry>
  77
  78    <varlistentry>
  79     <term><literal>RESET ( <replaceable class="PARAMETER">storage_parameter</replaceable> [, ... ] )</literal></term>
  80     <listitem>
  81      <para>
  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.
  85      </para>
  86     </listitem>
  87    </varlistentry>
  88
  89   </variablelist>
  90   </para>
  91
  92  </refsect1>
  93
  94  <refsect1>
  95   <title>Parameters</title>
  96
  97     <variablelist>
  98
  99      <varlistentry>
 100       <term><replaceable class="PARAMETER">name</replaceable></term>
 101       <listitem>
 102        <para>
 103         The name (possibly schema-qualified) of an existing index to
 104         alter.
 105        </para>
 106       </listitem>
 107      </varlistentry>
 108
 109      <varlistentry>
 110       <term><replaceable class="PARAMETER">new_name</replaceable></term>
 111       <listitem>
 112        <para>
 113         The new name for the index.
 114        </para>
 115       </listitem>
 116      </varlistentry>
 117
 118      <varlistentry>
 119       <term><replaceable class="PARAMETER">tablespace_name</replaceable></term>
 120       <listitem>
 121        <para>
 122         The tablespace to which the index will be moved.
 123        </para>
 124       </listitem>
 125      </varlistentry>
 126
 127      <varlistentry>
 128       <term><replaceable class="PARAMETER">storage_parameter</replaceable></term>
 129       <listitem>
 130        <para>
 131         The name of an index-method-specific storage parameter.
 132        </para>
 133       </listitem>
 134      </varlistentry>
 135
 136      <varlistentry>
 137       <term><replaceable class="PARAMETER">value</replaceable></term>
 138       <listitem>
 139        <para>
 140         The new value for an index-method-specific storage parameter.
 141         This might be a number or a word depending on the parameter.
 142        </para>
 143       </listitem>
 144      </varlistentry>
 145
 146     </variablelist>
 147  </refsect1>
 148
 149  <refsect1>
 150   <title>Notes</title>
 151
 152    <para>
 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.
 157    </para>
 158
 159    <para>
 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.
 164    </para>
 165
 166    <para>
 167     Changing any part of a system catalog index is not permitted.
 168    </para>
 169  </refsect1>
 170
 171  <refsect1>
 172   <title>Examples</title>
 173   <para>
 174    To rename an existing index:
 175 <programlisting>
 176 ALTER INDEX distributors RENAME TO suppliers;
 177 </programlisting>
 178   </para>
 179
 180   <para>
 181    To move an index to a different tablespace:
 182 <programlisting>
 183 ALTER INDEX distributors SET TABLESPACE fasttablespace;
 184 </programlisting>
 185   </para>
 186
 187   <para>
 188    To change an index's fill factor (assuming that the index method
 189    supports it):
 190 <programlisting>
 191 ALTER INDEX distributors SET (fillfactor = 75);
 192 REINDEX INDEX distributors;
 193 </programlisting>
 194   </para>
 195
 196  </refsect1>
 197
 198  <refsect1>
 199   <title>Compatibility</title>
 200
 201   <para>
 202    <command>ALTER INDEX</> is a <productname>PostgreSQL</productname>
 203    extension.
 204   </para>
 205  </refsect1>
 206
 207
 208  <refsect1>
 209   <title>See Also</title>
 210
 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>
 214   </simplelist>
 215  </refsect1>
 216 </refentry>