'\" t .\" Title: ALTER INDEX .\" Author: The PostgreSQL Global Development Group .\" Generator: DocBook XSL Stylesheets vsnapshot .\" Date: 2021 .\" Manual: PostgreSQL 13.4 Documentation .\" Source: PostgreSQL 13.4 .\" Language: English .\" .TH "ALTER INDEX" "7" "2021" "PostgreSQL 13.4" "PostgreSQL 13.4 Documentation" .\" ----------------------------------------------------------------- .\" * Define some portability stuff .\" ----------------------------------------------------------------- .\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ .\" http://bugs.debian.org/507673 .\" http://lists.gnu.org/archive/html/groff/2009-02/msg00013.html .\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ .ie \n(.g .ds Aq \(aq .el .ds Aq ' .\" ----------------------------------------------------------------- .\" * set default formatting .\" ----------------------------------------------------------------- .\" disable hyphenation .nh .\" disable justification (adjust text to left margin only) .ad l .\" ----------------------------------------------------------------- .\" * MAIN CONTENT STARTS HERE * .\" ----------------------------------------------------------------- .SH "NAME" ALTER_INDEX \- change the definition of an index .SH "SYNOPSIS" .sp .nf ALTER INDEX [ IF EXISTS ] \fIname\fR RENAME TO \fInew_name\fR ALTER INDEX [ IF EXISTS ] \fIname\fR SET TABLESPACE \fItablespace_name\fR ALTER INDEX \fIname\fR ATTACH PARTITION \fIindex_name\fR ALTER INDEX \fIname\fR [ NO ] DEPENDS ON EXTENSION \fIextension_name\fR ALTER INDEX [ IF EXISTS ] \fIname\fR SET ( \fIstorage_parameter\fR [= \fIvalue\fR] [, \&.\&.\&. ] ) ALTER INDEX [ IF EXISTS ] \fIname\fR RESET ( \fIstorage_parameter\fR [, \&.\&.\&. ] ) ALTER INDEX [ IF EXISTS ] \fIname\fR ALTER [ COLUMN ] \fIcolumn_number\fR SET STATISTICS \fIinteger\fR ALTER INDEX ALL IN TABLESPACE \fIname\fR [ OWNED BY \fIrole_name\fR [, \&.\&.\&. ] ] SET TABLESPACE \fInew_tablespace\fR [ NOWAIT ] .fi .SH "DESCRIPTION" .PP \fBALTER INDEX\fR 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\&. .PP RENAME .RS 4 The RENAME form changes the name of the index\&. If the index is associated with a table constraint (either UNIQUE, PRIMARY KEY, or EXCLUDE), the constraint is renamed as well\&. There is no effect on the stored data\&. .sp Renaming an index acquires a SHARE UPDATE EXCLUSIVE lock\&. .RE .PP SET TABLESPACE .RS 4 This form changes the index\*(Aqs 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 \fBALTER DATABASE\fR or explicit \fBALTER INDEX\fR invocations instead if desired\&. See also CREATE TABLESPACE (\fBCREATE_TABLESPACE\fR(7))\&. .RE .PP ATTACH PARTITION .RS 4 Causes the named index to become attached to the altered index\&. The named index must be on a partition of the table containing the index being altered, and have an equivalent definition\&. An attached index cannot be dropped by itself, and will automatically be dropped if its parent index is dropped\&. .RE .PP DEPENDS ON EXTENSION \fIextension_name\fR .br NO DEPENDS ON EXTENSION \fIextension_name\fR .RS 4 This form marks the index as dependent on the extension, or no longer dependent on that extension if NO is specified\&. An index that\*(Aqs marked as dependent on an extension is automatically dropped when the extension is dropped\&. .RE .PP SET ( \fIstorage_parameter\fR [= \fIvalue\fR] [, \&.\&.\&. ] ) .RS 4 This form changes one or more index\-method\-specific storage parameters for the index\&. See CREATE INDEX (\fBCREATE_INDEX\fR(7)) 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 \fBREINDEX\fR(7) to get the desired effects\&. .RE .PP RESET ( \fIstorage_parameter\fR [, \&.\&.\&. ] ) .RS 4 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\&. .RE .PP ALTER [ COLUMN ] \fIcolumn_number\fR SET STATISTICS \fIinteger\fR .RS 4 This form sets the per\-column statistics\-gathering target for subsequent \fBANALYZE\fR(7) operations, though can be used only on index columns that are defined as an expression\&. Since expressions lack a unique name, we refer to them using the ordinal number of the index column\&. The target can be set in the range 0 to 10000; alternatively, set it to \-1 to revert to using the system default statistics target (default_statistics_target)\&. For more information on the use of statistics by the PostgreSQL query planner, refer to Section\ \&14.2\&. .RE .SH "PARAMETERS" .PP IF EXISTS .RS 4 Do not throw an error if the index does not exist\&. A notice is issued in this case\&. .RE .PP \fIcolumn_number\fR .RS 4 The ordinal number refers to the ordinal (left\-to\-right) position of the index column\&. .RE .PP \fIname\fR .RS 4 The name (possibly schema\-qualified) of an existing index to alter\&. .RE .PP \fInew_name\fR .RS 4 The new name for the index\&. .RE .PP \fItablespace_name\fR .RS 4 The tablespace to which the index will be moved\&. .RE .PP \fIextension_name\fR .RS 4 The name of the extension that the index is to depend on\&. .RE .PP \fIstorage_parameter\fR .RS 4 The name of an index\-method\-specific storage parameter\&. .RE .PP \fIvalue\fR .RS 4 The new value for an index\-method\-specific storage parameter\&. This might be a number or a word depending on the parameter\&. .RE .SH "NOTES" .PP These operations are also possible using ALTER TABLE (\fBALTER_TABLE\fR(7))\&. \fBALTER INDEX\fR is in fact just an alias for the forms of \fBALTER TABLE\fR that apply to indexes\&. .PP There was formerly an \fBALTER INDEX OWNER\fR variant, but this is now ignored (with a warning)\&. An index cannot have an owner different from its table\*(Aqs owner\&. Changing the table\*(Aqs owner automatically changes the index as well\&. .PP Changing any part of a system catalog index is not permitted\&. .SH "EXAMPLES" .PP To rename an existing index: .sp .if n \{\ .RS 4 .\} .nf ALTER INDEX distributors RENAME TO suppliers; .fi .if n \{\ .RE .\} .PP To move an index to a different tablespace: .sp .if n \{\ .RS 4 .\} .nf ALTER INDEX distributors SET TABLESPACE fasttablespace; .fi .if n \{\ .RE .\} .PP To change an index\*(Aqs fill factor (assuming that the index method supports it): .sp .if n \{\ .RS 4 .\} .nf ALTER INDEX distributors SET (fillfactor = 75); REINDEX INDEX distributors; .fi .if n \{\ .RE .\} .PP Set the statistics\-gathering target for an expression index: .sp .if n \{\ .RS 4 .\} .nf CREATE INDEX coord_idx ON measured (x, y, (z + t)); ALTER INDEX coord_idx ALTER COLUMN 3 SET STATISTICS 1000; .fi .if n \{\ .RE .\} .SH "COMPATIBILITY" .PP \fBALTER INDEX\fR is a PostgreSQL extension\&. .SH "SEE ALSO" CREATE INDEX (\fBCREATE_INDEX\fR(7)), \fBREINDEX\fR(7)