Provided by: postgresql-client-8.3_8.3.8-1_i386 bug


       CREATE INDEX - define a new index


       CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] name ON table [ USING method ]
           ( { column | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
           [ WITH ( storage_parameter = value [, ... ] ) ]
           [ TABLESPACE tablespace ]
           [ WHERE predicate ]


       CREATE  INDEX constructs an index name on the specified table.  Indexes
       are  primarily   used   to   enhance   database   performance   (though
       inappropriate use can 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, hash, GiST, and GIN.
       Users can also define their own  index  methods,  but  that  is  fairly

       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 in the documentation for more

       The expression used in the WHERE clause can 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.

              When  this  option  is  used,  PostgreSQL  will  build the index
              without  taking  any  locks  that  prevent  concurrent  inserts,
              updates, or deletes on the table; whereas a standard index build
              locks out writes (but not reads) on the table until  it’s  done.
              There  are several caveats to be aware of when using this option
              — see Building Indexes Concurrently [create_index(7)].

       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  index  method to be used. Choices are btree,
              hash, gist, and gin. 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 can be  omitted
              if the expression has the form of a function call.

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

       ASC    Specifies ascending sort order (which is the default).

       DESC   Specifies descending sort order.

              Specifies  that nulls sort before non-nulls. This is the default
              when DESC is specified.

              Specifies that nulls sort after non-nulls. This is  the  default
              when DESC is not specified.

              The  name  of  an  index-method-specific  storage parameter. See
              below for details.

              The tablespace in which to create the index. If  not  specified,
              default_tablespace is consulted, or temp_tablespaces for indexes
              on temporary tables.

              The constraint expression for a partial index.

       The WITH clause can specify storage parameters for indexes. Each  index
       method can have its own set of allowed storage parameters. The built-in
       index methods all accept a single parameter:

              The fillfactor for an index is a percentage that determines  how
              full the index method will try to pack index pages. For B-trees,
              leaf pages are filled to this percentage  during  initial  index
              build,  and  also when extending the index at the right (largest
              key values). If pages subsequently become completely full,  they
              will  be  split,  leading  to gradual degradation in the index’s
              efficiency. B-trees use a default  fillfactor  of  90,  but  any
              value  from  10  to 100 can be selected.  If the table is static
              then fillfactor 100 is best to  minimize  the  index’s  physical
              size,  but  for  heavily  updated tables a smaller fillfactor is
              better to minimize the need for page  splits.  The  other  index
              methods  use fillfactor in different but roughly analogous ways;
              the default fillfactor varies between methods.

       Creating an index can interfere with regular operation of  a  database.
       Normally  PostgreSQL  locks  the table to be indexed against writes and
       performs the entire index build with a single scan of the table.  Other
       transactions  can  still  read  the  table,  but if they try to insert,
       update, or delete rows in the table they will  block  until  the  index
       build  is  finished. This could have a severe effect if the system is a
       live production database. Very large tables can take many hours  to  be
       indexed,  and  even  for  smaller  tables,  an index build can lock out
       writers for periods that are unacceptably long for a production system.

       PostgreSQL  supports  building indexes without locking out writes. This
       method is invoked by  specifying  the  CONCURRENTLY  option  of  CREATE
       INDEX.   When this option is used, PostgreSQL must perform two scans of
       the table, and in addition it must wait for all  existing  transactions
       that  could  potentially  use  the index to terminate. Thus this method
       requires more  total  work  than  a  standard  index  build  and  takes
       significantly  longer  to  complete.  However,  since  it allows normal
       operations to continue while the index is built, this method is  useful
       for  adding  new  indexes  in  a production environment. Of course, the
       extra CPU and I/O load imposed by the index creation might  slow  other

       In  a  concurrent  index  build, the index is actually entered into the
       system catalogs in one transaction, then the two table scans occur in a
       second  and  third transaction.  If a problem arises while scanning the
       table, such as a uniqueness violation in a  unique  index,  the  CREATE
       INDEX  command  will  fail  but leave behind an ‘‘invalid’’ index. This
       index will be  ignored  for  querying  purposes  because  it  might  be
       incomplete;  however it will still consume update overhead. The psql \d
       command will mark such an index as INVALID:

       postgres=# \d tab
              Table ""
        Column |  Type   | Modifiers
        col    | integer |
           "idx" btree (col) INVALID

       The recommended recovery method in such cases is to drop the index  and
       try again to perform CREATE INDEX CONCURRENTLY. (Another possibility is
       to rebuild the index with REINDEX.  However,  since  REINDEX  does  not
       support concurrent builds, this option is unlikely to seem attractive.)

       Another caveat when building a unique index concurrently  is  that  the
       uniqueness   constraint   is   already  being  enforced  against  other
       transactions when  the  second  table  scan  begins.  This  means  that
       constraint  violations  could be reported in other queries prior to the
       index becoming available for use, or even  in  cases  where  the  index
       build  eventually  fails.  Also,  if a failure does occur in the second
       scan,  the  ‘‘invalid’’  index  continues  to  enforce  its  uniqueness
       constraint afterwards.

       Concurrent  builds  of  expression  indexes  and  partial  indexes  are
       supported.  Errors occurring in the  evaluation  of  these  expressions
       could  cause  behavior  similar  to  that  described  above  for unique
       constraint violations.

       Regular index builds permit other regular  index  builds  on  the  same
       table  to  occur  in  parallel, but only one concurrent index build can
       occur on a table at a time. In both cases, no  other  types  of  schema
       modification  on the table are allowed meanwhile. Another difference is
       that  a  regular  CREATE  INDEX  command  can  be  performed  within  a
       transaction block, but CREATE INDEX CONCURRENTLY cannot.


       See  in  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 can 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  in  the  documentation  and in in the

       For index methods that support ordered scans (currently, only  B-tree),
       the  optional  clauses ASC, DESC, NULLS FIRST, and/or NULLS LAST can be
       specified to reverse the normal sort direction of the index.  Since  an
       ordered  index  can  be  scanned  either forward or backward, it is not
       normally useful to create  a  single-column  DESC  index  —  that  sort
       ordering  is already available with a regular index. The value of these
       options is that multicolumn indexes can be created that match the  sort
       ordering  requested by a mixed-ordering query, such as SELECT ... ORDER
       BY x ASC, y DESC. The NULLS options are useful if you need  to  support
       ‘‘nulls  sort  low’’  behavior,  rather  than  the default ‘‘nulls sort
       high’’, in queries that depend on indexes to avoid sorting steps.

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

       Prior releases of PostgreSQL also had  an  R-tree  index  method.  This
       method  has  been removed because it had no significant advantages over
       the GiST method.  If  USING  rtree  is  specified,  CREATE  INDEX  will
       interpret  it as USING gist, to simplify conversion of old databases to


       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 expression lower(title),  allowing  efficient
       case-insensitive searches:

       CREATE INDEX lower_title_idx ON films ((lower(title)));

       To create an index with non-default sort ordering of nulls:

       CREATE INDEX title_idx_nulls_low ON films (title NULLS FIRST);

       To create an index with non-default fill factor:

       CREATE UNIQUE INDEX title_idx ON films (title) WITH (fillfactor = 70);

       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;

       To create an index without locking out writes to the table:

       CREATE INDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity);


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