Practical PostgreSQL

Create Index

Places an index on a table.

Synopsis

CREATE [ UNIQUE ] INDEX index_name ON table
       [ USING method ] ( column [ op_class ] [, ...] )
CREATE [ UNIQUE ] INDEX index_name ON table
       [ USING method ] ( func_name ( column [, ... ] ) [ op_class ] )

Parameters

UNIQUE

The optional UNIQUE keyword. When used, this causes the database to check for, and prevent, duplicate values within the column (or combined columns) it is placed upon. This check will occur both when the index is created and each time data is added to the table. PostgreSQL will then generate an error whenever an INSERT or UPDATE request is made that would place duplicate data within the index, and the command will fail.

index_name

The name for the new index.

table

The name of the table you are placing the index on.

method

The type of indexing method you wish to use for the index. There are three methods available to choose from, the default being btree:

btree

The PostgreSQL implementation of Lehman-Yao high-concurrency B-trees.

rtree

The PostgreSQL implementation of standard R-trees using Guttman’s quadratic split algorithm.

hash

The PostgreSQL implementation of Litwin’s linear hashing.

column

The name of the column (or comma-delimited list of columns) on which to place the index.

op_class

The optionally specified associated operator class. For most users, this should not be specified.

func_name

The name of a function you wish CREATE INDEX to use on the specified columns (rather than indexing the data values literally in those columns). ...