Create a index in PostgreSQL

Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes, they are just used to speed up queries.

Updating a table containing indexes takes more time than updating a table without, this is because the indexes also need an update. So, it is a good idea to create indexes only on columns that are often used for a search.

CREATE [ UNIQUE ] INDEX name ON table [ USING method ]
( { column | ( expression ) } [ opclass ] [, ...] )
[ TABLESPACE tablespace ]
[ WHERE predicate ]

Parameters

UNIQUE

Causes the system to check for duplicate values in the table when the index is created (if data already exist) and each time data is added. Attempts to insert or update data which would result in duplicate entries will generate an error.

name

The name of the index to be created. No schema name can be included here; the index is always created in the same schema as its parent table.

table

The name (possibly schema-qualified) of the table to be indexed.

method

The name of the method to be used for the index. Choices are btree, hash, rtree, and gist. The default method is btree.

column

The name of a column of the table.

expression

An expression based on one or more columns of the table. The expression usually must be written with surrounding parentheses, as shown in the syntax. However, the parentheses may be omitted if the expression has the form of a function call.

opclass

The name of an operator class. See below for details.

tablespace

The tablespace in which to create the index. If not specified, default_tablespace is used, or the database's default tablespace if default_tablespace is an empty string.

predicate

The constraint expression for a partial index.


A Unique Index

Creates a unique index on a table. A unique index means that two rows cannot have the same index value.

CREATE UNIQUE INDEX index_name ON table_name (column_name)

The "column_name" specifies the column you want indexed.

A Simple Index

Creates a simple index on a table. When the UNIQUE keyword is omitted, duplicate values are allowed.

CREATE INDEX index_name ON table_name (column_name)

The "column_name" specifies the column you want indexed.

Examples

To create a B-tree index on the column title in the table films:

CREATE UNIQUE INDEX title_idx ON films (title);

To create an index on the column code in the table films and have the index reside in the tablespace indexspace:

CREATE INDEX code_idx ON films(code) TABLESPACE indexspace;