ALTER INDEX

Description

Modifies the definition of an existing index.

Precautions

• Only the index owner, a user who has the INDEX permission on the table where the index resides, or a user who has the ALTER ANY INDEX permission can run the ALTER INDEX command. When separation of duties is disabled, a system administrator has this permission by default.
• Do not keep a large number of invisible indexes on the same base table. Otherwise, the performance of DML operations such as INSERT, UPDATE, and DELETE may be affected.

Syntax

• Rename a table index.

  ALTER INDEX [ IF EXISTS ] index_name 
      RENAME TO new_name;

  

• Set the table index to be unavailable.

  ALTER INDEX [ IF EXISTS ] index_name UNUSABLE;

• Rename an index partition.

  ALTER INDEX [ IF EXISTS ] index_name 
      RENAME PARTITION index_partition_name TO new_index_partition_name;

  

Parameters

• index_name

  Specifies the index name to be modified.

• IF EXISTS

  Sends a notice instead of an error if the specified index does not exist.

• RENAME TO new_name

  Changes only the name of the index. The stored data is not affected.

  new_name

  Specifies the new name of the index.

  Value range: a string that complies with Identifier Description.

• RENAME PARTITION index_partition_name TO new_index_partition_name

  Renames an index partition.

• new_index_partition_name

  Specifies the new name of the index partition.

• index_partition_name

  Specifies the name of an index partition.

Examples

• Rename an index.

  -- Create the test1 table and create an index for it.
  m_db=# CREATE TABLE test1(col1 INT, col2 INT);
  m_db=# CREATE INDEX aa ON test1(col1);
  
  -- Rename index aa to idx_test1_col1.
  m_db=# ALTER INDEX aa RENAME TO idx_test1_col1;
  
  -- Query the index information in the test1 table.
  m_db=# SELECT tablename,indexname,tablespace FROM pg_indexes WHERE tablename = 'test1';
   tablename |   indexname    | tablespace 
  -----------+----------------+------------
   test1     | idx_test1_col1 | 
  (1 row)

• Rename an index partition.

  -- Create the partitioned table test2.
  m_db=# CREATE TABLE test2(col1 int, col2 int) PARTITION BY RANGE (col1)(
      PARTITION p1 VALUES LESS THAN (100),
      PARTITION p2 VALUES LESS THAN (200)
  );
  
  -- Create a partitioned index.
  m_db=# CREATE INDEX idx_test2_col1 ON test2(col1) LOCAL(
      PARTITION p1,
      PARTITION p2
  );
  -- Rename the index partition.
  m_db=# ALTER INDEX idx_test2_col1 RENAME PARTITION p1 TO p1_test2_idx;
  m_db=# ALTER INDEX idx_test2_col1 RENAME PARTITION p2 TO p2_test2_idx;
  
  -- Query the partition name of the idx_test2_col1 index.
  m_db=# SELECT relname FROM pg_partition WHERE parentid = 'idx_test2_col1'::regclass;
     relname    
  --------------
   p1_test2_idx
   p2_test2_idx
  (2 rows)

Helpful Links

CREATE INDEX, DROP INDEX, REINDEX