PostgreSQL: Indexes

In this PostgreSQL post explains how to create, drop, and rename indexes in PostgreSQL with syntax and examples.

What is an Index in PostgreSQL?

An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns.

Create an Index

You can create an index in PostgreSQL using the CREATE INDEX statement.

Syntax

The syntax to create an index using the CREATE INDEX statement in PostgreSQL is:

CREATE [UNIQUE] INDEX [CONCURRENTLY] index_name
  [ USING BTREE | HASH | GIST | SPGIST | GIN ]
  ON table_name
    (index_col1 [ASC | DESC], 
     index_col2 [ASC | DESC],
     ...
     index_col_n [ASC | DESC]);

UNIQUE

Optional. The UNIQUE modifier indicates that the combination of values in the indexed columns must be unique.

CONCURRENTLY

Optional. When the index is created, it will not lock the table. By default, the table is locked while the index is being created.

index_name

The name to assign to the index.

table_name

The name of the table in which to create the index.

index_col1, index_col2, ... index_col_n

The columns to use in the index.

ASC

Optional. The index is sorted in ascending order for that column.

DESC

Optional. The index is sorted in descending order for that column.

Example

Let's look at an example of how to create an index in PostgreSQL.

For example:

CREATE INDEX order_details_idx
  ON order_details (order_date);

In this example, the CREATE INDEX statement would create an index called order_details_idx that consists of the order_date field.

Unique Index

To create a unique index on a table, you need to specify the UNIQUE keyword when creating the index.

For example:

CREATE UNIQUE INDEX order_details_idx
  ON order_details (order_date, note);

In this example, we would create a unique index on order_details table that consists of the order_date and note fields so that the combination of these fields must always contain a unique value with no duplicates. This is a great way to enforce integrity within your database if you require unique values in columns that are not part of your primary key.

Drop an Index

You can drop an index in PostgreSQL using the DROP INDEX statement.

Syntax

The syntax to drop an index using the DROP INDEX statement in PostgreSQL is:

DROP INDEX [CONCURRENTLY] [IF EXISTS] index_name
  [ CASCADE | RESTRICT ];

CONCURRENTLY

Optional. When the index is dropped, it will not lock the table. By default, the table is locked while the index is being removed from the table.

IF EXISTS

Optional. If specified, the DROP INDEX statement will not raise an error if the index does not exist.

index_name

The name of the index to drop.

CASCADE

Optional. All objects that depend on this index are also dropped.

RESTRICT

Optional. Index will not be dropped if there are objects that depend on the index.

Example

Let's look at an example of how to drop an index in PostgreSQL.

For example:

DROP INDEX order_details_idx;

In this example, we've dropped an index called websites_idx from the websites table.

Rename an Index

You can rename an index in PostgreSQL using the ALTER INDEX statement.

Syntax

The syntax to rename an index using the ALTER INDEX statement is:

ALTER INDEX [IF EXISTS] index_name,
  RENAME TO new_index_name;

IF EXISTS

Optional. If specified, the ALTER INDEX statement will not raise an error if the index does not exist.

index_name

The name of the index that you wish to rename.

new_index_name

The new name for the index.

Example

Let's look at an example of how to rename an index in PostgreSQL.

For example:

ALTER INDEX order_details_idx
  RENAME TO od_new_index;

In this example, we've renamed the index called order_details_idx to od_new_index.