DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH
 

/usr/man2/cat.l/create_index.l.Z





NAME

       CREATE INDEX - define a new index


SYNOPSIS

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


DESCRIPTION

       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 alter-
       natively 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-concur-
       rency  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  par-
       tial  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
       discussion.

       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  argu-
       ments  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.


PARAMETERS

       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.

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

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

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

       predicate
              The constraint expression for a partial index.


NOTES

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

       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 func-
       tions 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 documenta-
       tion.

       Use DROP INDEX [drop_index(l)] 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
       predicate.


EXAMPLES

       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;


COMPATIBILITY

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


SEE ALSO

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

SQL - Language Statements         2005-11-05                    CREATE INDEX()

Man(1) output converted with man2html