create_index manpage

Search topic Section
Get manual page for the search topic
List all commands matching the search topic
List all topics in the manpage index


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

       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.

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

       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 provi-
       sions for indexes in the SQL standard.

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

SQL - Language Statements	  2010-12-14			CREATE INDEX()
YoLinux.com Home Page
YoLinux Tutorial Index
Privacy Policy | Advertise with us | Feedback Form |
Unauthorized copying or redistribution prohibited.
    Bookmark and Share