Changes the definition of an index.
ALTER INDEX [ IF EXISTS ] <name> RENAME TO <new_name> ALTER INDEX [ IF EXISTS ] <name> SET TABLESPACE <tablespace_name> ALTER INDEX [ IF EXISTS ] <name> SET ( <storage_parameter> = <value> [, ...] ) ALTER INDEX [ IF EXISTS ] <name> RESET ( <storage_parameter> [, ...] ) ALTER INDEX ALL IN TABLESPACE <name> [ OWNED BY <role_name> [, ... ] ] SET TABLESPACE <new_tablespace> [ NOWAIT ]
ALTER INDEX changes the definition of an existing index. There are several subforms:
CREATEprivilege on the new tablespace. All indexes in the current database in a tablespace can be moved by using the
ALL IN TABLESPACEform, 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
NOWAIToption 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 DATABASEor explicit
ALTER INDEXinvocations instead if desired. See also
REINDEXto rebuild the index to get the desired effects.
REINDEXmay be needed to update the index entirely.
These operations are also possible using
Changing any part of a system catalog index is not permitted.
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;
ALTER INDEX is a Greenplum Database extension.
Parent topic: SQL Commands