Unique indices

Specifying the UNIQUE keyword causes the index to disallow duplicate values within the column (or set of columns) it indexes. Creating a UNIQUE index on a table is functionally identical to creating a table with the UNIQUE constraint (see the Section called Using Constraints" later in this chapter).

Example 7-3 creates a unique index called unique_ publisher_idx on the publishers table's name column. This will disallow two publishers from having the same name in this table.

booktown=# CREATE UNIQUE INDEX unique_publisher_idx
booktown-#                     ON publishers (name);
CREATE
booktown=# \d publishers
       Table "publishers"
 Attribute |  Type   | Modifier
-----------+---------+----------
 id        | integer | not null
 name      | text    |
 address   | text    |
Indices: publishers_pkey,
         unique_publisher_idx

As the NULL value does not technically match any value, duplicate instances of NULL can be inserted into a column with a unique index placed on it. This is the main practical difference between a unique index and an index implicitly created by the PRIMARY KEY constraint, which does not allow NULL values at all.

Index types

The optional USING clause can be used to specify the type of index to implement. PostgreSQL 7.1.x supports three types of indices including:

• B-tree

• R-tree

• Hash

PostgreSQL's B-tree implementation uses Lehman-Yao high-concurrency B-tree algorithms and is both the most capable, and most commonly used indexing method. For this reason, it is the default index type.

The R-tree implementation is primarily useful for spacial data type operations (i.e., operations on geometric types) and utilizes Guttman's quadratic split algorithm. The Hash implementation utilizes Litwin's linear hashing routines, which have been traditionally used for indices that involve frequent direct equal-to comparisons (e.g., with the = operator).

At the time of this writing, for PostgreSQL 7.1.x, the B-tree index implementation is by far the most capable and flexible of the available index types. At this time, it is recommended that you use the B-tree index type over the Hash implementation, even for direct = comparisons. The Hash index exists mostly for legacy reasons, though it may still be used if you are sure your system would benefit from a Hash index over a B-tree index.

As stated, the R-tree index implementation is recommended for indexing geometric types; be aware, however, that several limitations exist. For example, you cannot create a unique R-tree index, nor can you create an R-tree index on more than one column. In these cases, it makes more sense to rely on the capable B-tree index type.

The USING clause can be used with the keywords BTREE, RTREE, and HASH in order to explicitly choose the type of index you want to create. Specifying BTREE explicitly chooses the default index type.

Example 7-4 creates a table called polygons, which stores spacial data of the type polygon. An index named spacial_idx is then applied to its shape column with the R-tree implementation.

booktown=# CREATE TABLE polygons (shape polygon);
CREATE
booktown=# CREATE INDEX spacial_idx ON polygons USING RTREE (shape);
CREATE

Functional indices

A slightly modified form of the CREATE INDEX command can be used to index the results of a function on a column value, rather than the column value itself. This is called a functional index.

Use the following syntax to create a functional index:

  CREATE [ UNIQUE ] INDEX indexname ON table
         [ USING indextype ] ( functionname ( column [, ...] ) [ opclass ] )

The only difference in this syntax is that the index is created on the results of the specified function applied to each column value. All other clauses have the same effect as the functional index.

Functional indices are useful on table columns that commonly have their values prepared through a function before being compared against values in a SQL statement. For example, the upper() function is commonly used to make case-insensitive comparisons. Creating an index using the upper() function enables such case-insensitive comparisons to be carried out efficiently.

Example 7-5 creates a functional index named upper_title_idx on the books table. It uses the upper() function on the title column as the basis to create the index. Then it performs an example SQL query that is more efficiently executed as a result of the newly created functional index.

booktown=# CREATE INDEX upper_title_idx ON books
booktown-#              (upper(title));
CREATE
booktown=# SELECT title FROM books WHERE upper(title) = 'DUNE';
 title
-------
 Dune
(1 row)