PostgreSQL is used to create databases, store massive data, and then access it from tables using different queries. Queries scan the complete table to fetch data for the query/code run by the user and might be time-consuming as it has to run through massive data. To save time and effort for the query optimizer, PostgreSQL allows the user to create indexes which helps in scanning the data.

This guide will explain how to alter an index in PostgreSQL.

How to Alter an INDEX in PostgreSQL?

Index creation in the PostgreSQL database can be very helpful in accessing data quickly from complex databases. The PostgreSQL DBMS enables the user to change the configurations of the existing indexes by running ALTER INDEX statement with different clauses. It can be used to change multiple aspects of the index such as its name, setting tablespace, etc.

Syntax

The following code block contains multiple syntaxes to use the Alter index in PostgreSQL.

ALTER INDEX [ IF EXISTS ] index_name RENAME TO modified_name;
 ALTER INDEX [ IF EXISTS ] index_name SET TABLESPACE   modifies_tablespace;
 ALTER INDEX index_name [ NO ] DEPENDS ON EXTENSION   new_extension_name;
 ALTER INDEX [ IF EXISTS ] index_name SET (storage_parameter [= value] [,   ... ] );
 ALTER INDEX [ IF EXISTS ] index_name RESET (storage_parameter [, ... ] );
 ALTER INDEX ALL IN TABLESPACE index_name [OWNED BY role_name [, ... ] ]
  SET TABLESPACE   new_tablespace_name [ NOWAIT ];

Here:

- ALTER INDEX statement is used to change the name of the index by using the RENAME TO clause containing the new name of the index in it.
- It can be used with the SET TABLESPACE clause to alter/attach the tablespace to the index.
- A DEPENDS ON EXTENSION clause is used to mark the dependency of the index to the extension or a NO behind it ends the dependency.
- SET or RESET clauses can be used to attach storage parameters like fill factor to the index which is used for fine-tuning the index.

Example 1: Change Index Name

Execute the following query to create an index in the PostgreSQL database:

CREATE INDEX idx_country 
 ON person (country);

Type the name of the index to be created after the CREATE INDEX statement, apply it to the selected table using the ON clause, and specify the column_name within the small braces:

Use the given code to change the name of the index:

ALTER INDEX idx_country 
RENAME TO new_country_idx;

Type the name of the index to be altered after the ALTER INDEX statement and then use RENAME TO clause containing the new name of the index:

Example 2: Set Tablespace

Use the following query to alter the tablespace of the selected index:

ALTER INDEX new_country_idx 
 SET TABLESPACE pg_default;

The above command will set the tablespace as “pg_default”:

Example 3: Set Storage Parameters

Use the following query to change the storage parameters of a Postgres index:

ALTER INDEX new_country_idx
 SET (fillfactor = 70);

The above query uses ALTER INDEX statement along with the SET keyword to set the fill factor parameter of the storage to 70:

That’s all about altering an index in PostgreSQL.

Conclusion

ALTER INDEX statement is used to make changes to the existing indexes created on the PostgreSQL database tables. Indexes are created to ease the work of the query optimizer by just scanning the indexes for accessing the data rather than going through the complete table. This guide has explained how to alter the index in the PostgreSQL database.