Description

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

IF EXISTS

Do not throw an error if the index does not exist. A
notice is issued in this case.

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
specified tablespace and moves the data file(s) associated
with the index to the new tablespace. To change the
tablespace of an index, you must own the index and have
CREATE privilege on the new
tablespace. All indexes in the current database in a
tablespace can be moved by using the ALL IN TABLESPACE form, which will lock all
indexes to be moved and then move each one. This form also
supports OWNED BY, which will only
move indexes owned by the roles specified. If the
NOWAIT option is specified then
the command will fail if it is unable to acquire all of the
locks required immediately. Note that system catalogs will
not be moved by this command, use ALTER
DATABASE or explicit ALTER
INDEX invocations instead if desired. See also
CREATE
TABLESPACE.

SET ( storage_parameter = value [, ... ] )

This form changes one or more index-method-specific
storage parameters for the index. See CREATE INDEX 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 REINDEX 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
might be needed to update the index entirely.

Parameters

name

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

new_name

The 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.

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
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
permitted.

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):