Create Index

Places an index on a table.


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 ] )



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.


The name for the new index.


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


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


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


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


The PostgreSQL implementation of Litwin’s linear hashing.


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


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


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). ...

Get Practical PostgreSQL now with the O’Reilly learning platform.

O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.