CREATE INDEX()			 SQL Commands			CREATE INDEX()



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



SQL - Language Statements	  2010-12-14			CREATE INDEX()