Skip to content
Snippets Groups Projects
Select Git revision
  • benchmark-tools
  • postgres-lambda
  • master default
  • REL9_4_25
  • REL9_5_20
  • REL9_6_16
  • REL_10_11
  • REL_11_6
  • REL_12_1
  • REL_12_0
  • REL_12_RC1
  • REL_12_BETA4
  • REL9_4_24
  • REL9_5_19
  • REL9_6_15
  • REL_10_10
  • REL_11_5
  • REL_12_BETA3
  • REL9_4_23
  • REL9_5_18
  • REL9_6_14
  • REL_10_9
  • REL_11_4
23 results

alter_index.sgml

Blame
  • alter_index.sgml 6.11 KiB
    <!--
    $PostgreSQL: pgsql/doc/src/sgml/ref/alter_index.sgml,v 1.13 2007/05/15 19:13:54 neilc Exp $
    PostgreSQL documentation
    -->
    
    <refentry id="SQL-ALTERINDEX">
     <refmeta>
      <refentrytitle id="sql-alterindex-title">ALTER INDEX</refentrytitle>
      <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>