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 anINSERT
orUPDATE
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). ...
Get Practical PostgreSQL now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.