Provided by: postgresql-client-8.0_8.0.7-2build1_i386 bug


       CREATE INDEX - define a new index


       CREATE [ UNIQUE ] INDEX name ON table [ USING method ]
           ( { column | ( expression ) } [ opclass ] [, ...] )
           [ TABLESPACE tablespace ]
           [ WHERE predicate ]


       CREATE  INDEX  constructs  an  index index_name on the specified table.
       Indexes are primarily used  to  enhance  database  performance  (though
       inappropriate use will result in slower performance).

       The  key  field(s)  for  the  index  are  specified as column names, or
       alternatively as expressions written in parentheses.   Multiple  fields
       can be specified if the index method supports multicolumn indexes.

       An  index field can be an expression computed from the values of one or
       more columns of the table row. This feature can be used to obtain  fast
       access  to  data  based  on  some transformation of the basic data. For
       example, an index computed on upper(col) would allow the  clause  WHERE
       upper(col) = ’JIM’ to use an index.

       PostgreSQL  provides  the index methods B-tree, R-tree, hash, and GiST.
       The B-tree index  method  is  an  implementation  of  Lehman-Yao  high-
       concurrency  B-trees.  The  R-tree  index method implements standard R-
       trees using Guttman’s quadratic split algorithm. The hash index  method
       is  an implementation of Litwin’s linear hashing. Users can also define
       their own index methods, but that is fairly complicated.

       When the WHERE clause is  present,  a  partial  index  is  created.   A
       partial index is an index that contains entries for only a portion of a
       table, usually a portion that is more useful for indexing than the rest
       of  the  table.  For  example,  if  you have a table that contains both
       billed and unbilled orders where the unbilled orders take  up  a  small
       fraction  of the total table and yet that is an often used section, you
       can improve performance by creating an  index  on  just  that  portion.
       Another  possible  application  is  to use WHERE with UNIQUE to enforce
       uniqueness over a subset of a table. See  the  documentation  for  more

       The  expression  used  in the WHERE clause may refer only to columns of
       the underlying table, but it can use all columns,  not  just  the  ones
       being indexed. Presently, subqueries and aggregate expressions are also
       forbidden in WHERE.  The same restrictions apply to index  fields  that
       are expressions.

       All  functions  and  operators  used  in  an  index  definition must be
       ‘‘immutable’’, that  is,  their  results  must  depend  only  on  their
       arguments  and  never on any outside influence (such as the contents of
       another table or the current time). This restriction ensures  that  the
       behavior  of  the index is well-defined. To use a user-defined function
       in an index expression or WHERE clause, remember to mark  the  function
       immutable when you create it.


       UNIQUE Causes  the  system  to  check for duplicate values in the table
              when the index is created (if data already exist) and each  time
              data  is  added.  Attempts  to insert or update data which would
              result in duplicate entries will generate an error.

       name   The name of the index to be  created.  No  schema  name  can  be
              included here; the index is always created in the same schema as
              its parent table.

       table  The name (possibly schema-qualified) of the table to be indexed.

       method The  name  of  the  method to be used for the index. Choices are
              btree, hash, rtree, and gist. The default method is btree.

       column The name of a column of the table.

              An expression based on one or more columns  of  the  table.  The
              expression usually must be written with surrounding parentheses,
              as shown in the syntax. However, the parentheses may be  omitted
              if the expression has the form of a function call.

              The name of an operator class. See below for details.

              The  tablespace  in which to create the index. If not specified,
              default_tablespace is used, or the database’s default tablespace
              if default_tablespace is an empty string.

              The constraint expression for a partial index.


       See  the  documentation for information about when indexes can be used,
       when they are not used, and in which particular situations they can  be

       Currently,  only  the B-tree and GiST index methods support multicolumn
       indexes. Up to 32 fields may be specified by default.  (This limit  can
       be  altered  when  building PostgreSQL.) Only B-tree currently supports
       unique indexes.

       An operator class can be specified for each column  of  an  index.  The
       operator  class  identifies  the  operators to be used by the index for
       that column. For example, a B-tree index on  four-byte  integers  would
       use  the  int4_ops  class;  this  operator  class  includes  comparison
       functions for four-byte integers.  In  practice  the  default  operator
       class  for the column’s data type is usually sufficient. The main point
       of having operator classes is that for some data types, there could  be
       more than one meaningful ordering. For example, we might want to sort a
       complex-number data type either by absolute value or by real  part.  We
       could  do  this  by defining two operator classes for the data type and
       then selecting the proper class when making an index. More  information
       about   operator   classes   is   in   the  documentation  and  in  the

       Use DROP INDEX [drop_index(7)] to remove an index.

       Indexes are not used for IS NULL clauses by default.  The best  way  to
       use indexes in such cases is to create a partial index using an IS NULL


       To create a B-tree index on the column title in the table films:

       CREATE UNIQUE INDEX title_idx ON films (title);

       To create an index on the column code in the table films and  have  the
       index reside in the tablespace indexspace:

       CREATE INDEX code_idx ON films(code) TABLESPACE indexspace;


       CREATE   INDEX  is  a  PostgreSQL  language  extension.  There  are  no
       provisions for indexes in the SQL standard.


       ALTER INDEX [alter_index(7)], DROP INDEX [drop_index(l)]