alter_index(5)

ALTER INDEX()             SQL Commands              ALTER INDEX()

NAME

     ALTER INDEX - change the definition of an index

SYNOPSIS

     ALTER INDEX name RENAME TO new_name
     ALTER INDEX name SET TABLESPACE tablespace_name
     ALTER INDEX name SET ( storage_parameter = value [, ... ] )
     ALTER INDEX name RESET ( storage_parameter [, ... ] )

DESCRIPTION

     ALTER INDEX changes the definition  of  an  existing  index.
     There are several subforms:

     RENAME
          The RENAME form changes the name of the index. There is
          no effect on the stored data.

     SET TABLESPACE
          This form changes the index's tablespace to the  speci-
          fied  tablespace  and moves the data file(s) associated
          with the index to the new tablespace.  See also  CREATE
          TABLESPACE [create_tablespace(5)].

     SET ( storage_parameter = value [, ... ] )
          This form changes  one  or  more  index-method-specific
          storage  parameters  for  the  index.  See CREATE INDEX
          [create_index(5)] for details on the available  parame-
          ters. Note that the index contents will not be modified
          immediately by this command; depending on the parameter
          you  may  need to rebuild the index with REINDEX [rein-
          dex(5)] to get the desired effects.

     RESET ( storage_parameter [, ... ] )
          This form  resets  one  or  more  index-method-specific
          storage  parameters  to  their defaults. As with SET, a
          REINDEX may be needed to update the index entirely.

PARAMETERS

     name The name (possibly  schema-qualified)  of  an  existing
          index to alter.

     new_name
          New name for the index.

     tablespace_name
          The tablespace to which the index will be moved.

     storage_parameter
          The name of an index-method-specific storage parameter.

SQL - Language StatementLast change: 2008-01-03 1

ALTER INDEX()             SQL Commands              ALTER INDEX()

     value
          The new  value  for  an  index-method-specific  storage
          parameter.   This might be a number or a word depending
          on the parameter.

NOTES

     These  operations  are  also  possible  using  ALTER   TABLE
     [alter_table(5)].   ALTER INDEX is in fact just an alias for
     the forms of ALTER TABLE that apply to indexes.

     There was formerly an 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.

     Changing any part of a system catalog index is  not  permit-
     ted.

EXAMPLES

     To rename an existing index:

     ALTER INDEX distributors RENAME TO suppliers;

     To move an index to a different tablespace:

     ALTER INDEX distributors SET TABLESPACE fasttablespace;

     To change an index's fill factor (assuming  that  the  index
     method supports it):

     ALTER INDEX distributors SET (fillfactor = 75);
     REINDEX INDEX distributors;

COMPATIBILITY

     ALTER INDEX is a PostgreSQL extension.

SEE ALSO

     CREATE INDEX [create_index(5)], REINDEX [reindex(l)]

SQL - Language StatementLast change: 2008-01-03 2