How to Rename Index Postgres

This article leads to creating and renaming the index of relations. We have used PostgreSQL and Windows 10 to implement some elementary examples.

PostgreSQL Index

An index is a sort of structure that points out some specific rows or data faster as compared to fetching data by specifying the columns. We can create indexes by using a single or more column.

PostgreSQL Index Types

Each type of index has a different algorithm used according to the queries used for the conditions. For example, B-tree, Hash, Gist, GIN, and BRIN. These indexes are created by different methods. When we create any index without mentioning the type of index then PostgreSQL always make use of the B-tree index type by default

B-tree Indexes

B-tree is a type of index that stores the data in the sorted form and also allows searching and insertion, deletion access as well. B-tree indexes are used when columns are used as a comparison that uses operations like “=, <, >, BETWEEN, IN, IS NULL”.

Where Does PostgreSQL Store Indexes?

Indexes that are stored in PostgreSQL are considered secondary. This means that every index is placed in a separate position instead of storing with the table’s main area where data is present. This is known as a heap of the table.

Syntax of Rename an Index in PostgreSQL

The basic syntax of the rename feature of the index is quite simple as it contains few parameters.

ALTER INDEX [IF EXISTS] name_of_index,
RENAME TO new_indexname;

Alter keyword is used to make a change in the name of an index.

IF EXISTS

It is an optional parameter. If it is not used then PostgreSQL will not produce any error. But if you will use it then the command will search for the relevant index, and if it is not found, then an error will be shown.

Name_of_index

It shows the name of that index we want to change the name.

New_indexname

We write the new name that should be given to the index.

Implementation of Rename Index PostgreSQL

Implementation via psql shell
Implementation via pgAdmin dashboard

Implementation of Rename Index via psql Shell

On the successful configuration of PostgreSQL, you will be able to implement commands on both interfaces. Open psql shell, after that you will be able to provide your name and password to proceed further.

To elaborate on the concept of index renaming, we should have a relation on which we will create an index to rename it later on. Use a CREATE command for the new relation. We have created a table named country that will store the information regarding country continents. The table will have 3 columns.

>> Create table Country (id int, name varchar(10), Continent Varchar(10));

After the creation of a table, now we will insert values in the table using the INSERT command.

>> Insert into country (id, name, Continent) values (1, 'Srilanka', 'Asia'), (2, ‘Germany’,'Europe'), (3, 'Australia' ,'Australia' ), (4, 'China' , 'Asia'), (5, 'UK', 'Europe');

We have inserted 5 rows as can be seen through the last row displayed in the command. To see the values in the table, we will use a SELECT command here.

>> SELECT * from Country;

The asterisk sign is to fetch all the records of the relevant table.

By using a create index command, a new index will be formed. Similarly, you can create an index on the ‘Continent’ column as well. The simple syntax of index creation is that it will take the name of the index after the keyword ‘create’ and then the name of the table, on which the index is created is mentioned, along with the column name.

>> Create INDEX idx_Cname ON country (name);

This is how an index is created on a table. But if you already have created some indexes on a different table and you are not sure about the name of a particular relation or specific index, then you can check the names of all tables, index names with index definition.

>> SELECT tablename, indexname, indexdef FROM pg_indexes WHERE schemaname = 'public ORDER BY tablename, indexname;

This command contains all the information regarding a relation specified in a schema and to see them alphabetically, we have mentioned them by applying for order on the table name and index name.

You can see the resultant value of this command that contains the commands of index creation as well in the definition part.

If you want to rename the index of a specific table then the above command can be customized accordingly. For example, we want to see the index we have created for the above table then we will use the following command.

>> select indexname, indexdef FROM pg_indexes where tablename = 'country';

Or another option is to see the whole description of the table along with the indexes we use the below command. This is used to identify the type of index as well. The index type for idx_cname is btree.

>> \d country;

Rename Index

Now the specified index of the table country can be renamed easily. As you have seen the syntax above, we will rename the index by mentioning a new name.

>> ALTER INDEX idx_cname TO idx_new_cname;

The message will indicate that the index is now renamed. As we can create multi-indexes in a single command at a time, it is impossible to rename the multi-created indexes or the single-created indexes at a time. It will cause an error.

>> ALTER INDEX idx_new_2cname, idx_1cnmae RENAME TO idx_new_cname;

Hence, it is proved that to rename an index, you need to mention a single index, or you can use both the indexes in separate commands.

The indexes that are deleted mistakenly can be recreated by using a simple command to reindex the table that already has an index.

>> Reindex table residence;

RENAME Index via pgAdmin

Open the dashboard, provide a password, and then a connection will be built with the server. To rename the index, we will first see the already created indexes. As those indexes or relations that are created in the psql shell are automatically seen in the dashboard of pgAdmin because of the server connection. So we will go to the left panel and will expand the database, you will find the option of tables by expanding the schema. On the further expansion of tables, you can see the relevant table i.e., country. This will show the name of a created index.

Now, right-click on the index and then go to the query tool to create a new index here.

>> create INDEX idx_2cname ON country (name);

Again go to the left panel and refresh the indexes, you will see the newly created index is also mentioned here.

Apply the rename command to rename the newly created index.

You will observe the change of name of the index.

Conclusion

The article ‘How to rename index Postgres’ provides the examples and steps required in renaming already created indexes. We have created a table and then indexes are also created on specific columns. These indexes can be seen by using the two types of commands we have mentioned. By specifying the table and index, we can rename the index easily.