alter_index(7)

NAME#

ALTER_INDEX - change the definition of an index

SYNOPSIS#

ALTER INDEX [ IF EXISTS ] \nname\n RENAME TO \nnew_name\n
ALTER INDEX [ IF EXISTS ] \nname\n SET TABLESPACE \ntablespace_name\n
ALTER INDEX \nname\n ATTACH PARTITION \nindex_name\n
ALTER INDEX \nname\n [ NO ] DEPENDS ON EXTENSION \nextension_name\n
ALTER INDEX [ IF EXISTS ] \nname\n SET ( \nstorage_parameter\n [= \nvalue\n] [, ... ] )
ALTER INDEX [ IF EXISTS ] \nname\n RESET ( \nstorage_parameter\n [, ... ] )
ALTER INDEX [ IF EXISTS ] \nname\n ALTER [ COLUMN ] \ncolumn_number\n
\n
    SET STATISTICS \ninteger\n
ALTER INDEX ALL IN TABLESPACE \nname\n [ OWNED BY \nrole_name\n [, ... ] ]
\n
    SET TABLESPACE \nnew_tablespace\n [ NOWAIT ]

DESCRIPTION#

ALTER INDEX changes the definition of an existing index. There are several subforms described below. Note that the lock level required may differ for each subform. An ACCESS EXCLUSIVE lock is held unless explicitly noted. When multiple subcommands are listed, the lock held will be the strictest one required from any subcommand.

RENAME

Renaming an index acquires a SHARE UPDATE EXCLUSIVE lock.

SET TABLESPACE

ATTACH PARTITION index_name

DEPENDS ON EXTENSION extension_name NO DEPENDS ON EXTENSION extension_name

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

RESET ( storage_parameter [, ... ] )

ALTER [ COLUMN ] column_number SET STATISTICS integer

PARAMETERS#

IF EXISTS

column_number

name

new_name

tablespace_name

extension_name

storage_parameter

value

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

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

Set the statistics-gathering target for an expression index:

CREATE INDEX coord_idx ON measured (x, y, (z + t));
ALTER INDEX coord_idx ALTER COLUMN 3 SET STATISTICS 1000;

COMPATIBILITY#

ALTER INDEX is a PostgreSQL extension.

SEE ALSO#

CREATE INDEX (CREATE_INDEX(7)), REINDEX(7)