1) CREATE [ UNIQUE ] INDEX index_name ON table
[ USING acc_name ] ( column [ ops_name ] [, ...] )
2) CREATE [ UNIQUE ] INDEX index_name ON table
[ USING acc_name ] ( func_name( column [, ... ]) [ ops_name ] )
When CREATE INDEX is passed the UNIQUE parameter, the database will check for duplicate values in whatever table is designated. This check will occur both when the index is created and each time data is added to the table. The database whill then generate an error whenever an INSERT or UPDATE request is made which would place duplicate data within the table.
This parameter takes the name of the new index.
Use this parameter to provide the name of the table you are indexing.
Specify the type of access method you wish to use for the index with this parameter. There are three methods available to choose from, the default being BTREE.
This PostgreSQL implementation of Lehman-Yao high-concurrency btrees.
The PostgreSQL implementation of standard rtrees using Guttman's quadratic split algorithm.
The PostgreSQL implementation of Litwin's linear hashing.
This parameter should contain the name of the column.
This parameter is optional. You may use it to assign an associated operator class if there is more than one meaningful type of operator class that can be used for sorting. Available operator classes are kept in the pg_opclass system table.
Use this parameter to provide the name of a function you wish CREATE INDEX to use. The specified function must return a value that can be indexed.
Use CREATE INDEX to build an index on the specified table. Always remember that while indexes are designed to improve the performance and effectiveness of your database, using them without full understanding can decrease performance and effectiveness due to their mis-use.
Syntactically, there are two ways to use CREATE INDEX (if you wish to a see a further example, both are listed in the synopsis of the command).
First, you may create the index so that the key field (or multiple fields, if you chose the b-tree access method -- which supports more than one field) are a table's column names. This method allows you to define multi-column indexes.
This is what is normally considered an index; however, the second type of index can often be useful.
The second syntactical way to use CREATE INDEX is to define the index based on the returned value of a user-specified function (an example of how to do this is located in the Examples section; also, the syntax is displayed in the Synopsis section).
The PostgreSQL query optimizer will use different indexes for different operators used in a comparison. It will choose which type of index to used based on a pre-defined list.
Table 13-1. Operators vs. index
| Operator | Index |
|---|---|
| btree index | <, <=, >=, >> |
| rtree index | <<, &<, &>, >>, @, ~=, && |
| hash index | equal (=) |
You can specify an operator class for each column of an index; do this by using the operator parameter referenced in the parameter list. The benefit of specifying an operator class for a column is that on a select few data types there could be multiple meaningful ways to order data.