<REFENTRY ID="SQL-CREATEINDEX">
 <REFMETA>
  <REFENTRYTITLE>
   CREATE INDEX
  </REFENTRYTITLE>
  <REFMISCINFO>SQL - Language Statements</REFMISCINFO>
 </REFMETA>
 <REFNAMEDIV>
  <REFNAME>
   CREATE INDEX
  </REFNAME>
  <REFPURPOSE>
   Constructs a secondary index
  </REFPURPOSE>
 <REFSYNOPSISDIV>
  <REFSYNOPSISDIVINFO>
   <DATE>1998-04-15</DATE>
  </REFSYNOPSISDIVINFO>
  <SYNOPSIS>
   CREATE [UNIQUE] INDEX <replaceable class="parameter">index_name</replaceable>
          ON <replaceable class="parameter">table</replaceable> [USING <replaceable class="parameter">acc_name</replaceable> ]
          ( <replaceable class="parameter">column</replaceable> [<replaceable class="parameter">ops_name</replaceable>] [, ...] )
        
   CREATE [UNIQUE] INDEX <replaceable class="parameter">index_name</replaceable>
          ON <replaceable class="parameter">table</replaceable> [USING <replaceable class="parameter">acc_name</replaceable> ]
          ( <replaceable class="parameter">func_name</replaceable>( <replaceable class="parameter">column</replaceable> [, ... ]) <replaceable class="parameter">ops_name</replaceable> )
  </SYNOPSIS>
  
  <REFSECT2 ID="R2-SQL-CREATEINDEX-1">
   <REFSECT2INFO>
    <DATE>1998-04-15</DATE>
   </REFSECT2INFO>
   <TITLE>
    Inputs
   </TITLE>
   <PARA>
   </PARA>
   <VARIABLELIST>
    <VARLISTENTRY>
     <TERM>
     </TERM>
     <LISTITEM>
      <PARA>
       <VARIABLELIST>
	<VARLISTENTRY>
	 <TERM>
	  <function>UNIQUE</function>
	 </TERM>
	 <LISTITEM>
	  <PARA>
	   <function>UNIQUE</function> causes the system to check for
	   duplicate values when the index is created (if data
	   already exist) and each time data is added. Attempts to
	   insert or update non-duplicate data will generate an
	   error.
	  </PARA>
	 </LISTITEM>
	</VARLISTENTRY>
	<VARLISTENTRY>
	 <TERM>
	  <replaceable class="parameter">index_name</replaceable>
	 </TERM>
	 <LISTITEM>
	  <PARA>
	   The name of the index to be created.
	  </PARA>
	 </LISTITEM>
	</VARLISTENTRY>
	<VARLISTENTRY>
	 <TERM>
	  <replaceable class="parameter">table</replaceable>
	 </TERM>
	 <LISTITEM>
	  <PARA>
	   The name of the table to be indexed.
	  </PARA>
	 </LISTITEM>
	</VARLISTENTRY>
	<VARLISTENTRY>
	 <TERM>
	  <replaceable class="parameter">acc_name</replaceable>
	 </TERM>
	 <LISTITEM>
	  <PARA>
	   the name of the access method which is to be used for
	   the index. The default access method is BTREE.
	   Postgres provides three access methods for secondary indices:
	   <variablelist>
	    <varlistentry>
	     <term>BTREE</term>
	     <listitem>
	      <para>
	       an implementation of the Lehman-Yao
	       high-concurrency btrees.
	      </para>
	     </listitem>
	    </varlistentry>
	    <varlistentry>
	     <term>RTREE</term>
	     <listitem>
	      <para>implements standard rtrees using Guttman's
	       quadratic split algorithm.
	      </para>
	     </listitem>
	    </varlistentry>
	    <varlistentry>
	     <term>HASH</term>
	     <listitem>
	      <para>
	       an implementation of Litwin's linear hashing.
	      </para>
	     </listitem>
	    </varlistentry>
	   </variablelist>
	  </PARA>
	 </LISTITEM>
	</VARLISTENTRY>
	<VARLISTENTRY>
	 <TERM>
	  <replaceable class="parameter">column</replaceable>
	 </TERM>
	 <LISTITEM>
	  <PARA>
	   The name of a column of the table.
	  </PARA>
	 </LISTITEM>
	</VARLISTENTRY>
	<VARLISTENTRY>
	 <TERM>
	  <replaceable class="parameter">ops_name</replaceable> 
	 </TERM>
	 <LISTITEM>
	  <PARA>
	   An associated operator class.
	   The following select list returns all ops_names:
	   
	   <programlisting>
       SELECT am.amname AS acc_name,
              opc.opcname AS ops_name,
              opr.oprname AS ops_comp
         FROM pg_am am, pg_amop amop,
              pg_opclass opc, pg_operator opr
         WHERE amop.amopid = am.oid AND
               amop.amopclaid = opc.oid AND
               amop.amopopr = opr.oid
         ORDER BY acc_name, ops_name, ops_comp
	   </programlisting>
	  </PARA>
	 </LISTITEM>
	</VARLISTENTRY>
	<VARLISTENTRY>
	 <TERM>
	  <replaceable class="parameter">func_name</replaceable> 
	 </TERM>
	 <LISTITEM>
	  <PARA>
	   A user-defined function, which returns a value that can
	   be indexed.
	  </PARA>
	 </LISTITEM>
	</VARLISTENTRY>
       </variablelist>
     </LISTITEM>
    </VARLISTENTRY>
   </VARIABLELIST>
  </REFSECT2>
  
  <REFSECT2 ID="R2-SQL-CREATEINDEX-2">
   <REFSECT2INFO>
    <DATE>1998-04-15</DATE>
   </REFSECT2INFO>
   <TITLE>
    Outputs
   </TITLE>
   <PARA>
   </PARA>
   <VARIABLELIST>
    <VARLISTENTRY>
     <TERM>
     </TERM>
     <LISTITEM>
      <PARA>
       <VARIABLELIST>
	<VARLISTENTRY>
	 <TERM>
	  <ReturnValue>CREATE</ReturnValue>
	 </TERM>
	 <LISTITEM>
	  <PARA>
	   The message returned if the index is successfully created.
	  </PARA>
	 </LISTITEM>
	</VARLISTENTRY>
	<VARLISTENTRY>
	 <TERM>
	  <ReturnValue>ERROR: Cannot create index: 'index_name' already exists.</ReturnValue>
	 </TERM>
	 <LISTITEM>
	  <PARA>
	   This error occurs if it is impossible to create the index.
	  </PARA>
	 </LISTITEM>
	</VARLISTENTRY>
       </variablelist>
     </LISTITEM>
    </VARLISTENTRY>
   </VARIABLELIST>
  </REFSECT2>
 </REFSYNOPSISDIV>
 
 <REFSECT1 ID="R1-SQL-CREATEINDEX-1">
  <REFSECT1INFO>
   <DATE>1998-04-15</DATE>
  </REFSECT1INFO>
  <TITLE>
   Description
  </TITLE>
  <PARA>
   This command constructs an index called <replaceable class="parameter">index_name</replaceable>.
  </PARA>
  <para>
   In the first syntax shown above, the key fields for the
   index are specified as column names; a column may also have
   an associated operator class. An operator class is used
   to specify the operators to be used for a particular
   index. For example, a btree index on four-byte integers
   would use the <literal>int4_ops</literal> class;
   this operator class includes
   comparison functions for four-byte integers. The default
   operator class is the appropriate operator class for that
   field type.
  </para>
   <para>
    In the second syntax, an index is defined
    on the result of a user-defined function
    <replaceable class="parameter">func_name</replaceable> applied
    to one or more attributes of a single class. These functional
    indices can be used to obtain fast access to data
    based on operators that would normally require some
    transformation to apply them to the base data.
   </para>
  
  <REFSECT2 ID="R2-SQL-CREATEINDEX-3">
   <REFSECT2INFO>
    <DATE>1998-04-15</DATE>
   </REFSECT2INFO>
   <TITLE>
    Notes
   </TITLE>
   <PARA>
    Currently, only the BTREE access method supports multi-column
    indices. Up to 7 keys may be specified.
   </PARA>
   <para>
    Use the <citerefentry>
     <refentrytitle>DROP INDEX</refentrytitle>
    </citerefentry>
    statement to remove indexes.
   </para>
  </REFSECT2>
  
 <REFSECT1 ID="R1-SQL-CREATEINDEX-2">
  <TITLE>
   Usage
  </TITLE>
  <PARA>To create a btree index on the field <literal>title</literal>
   in the table <literal>films</literal>:
  </PARA>
  <ProgramListing>
   CREATE UNIQUE INDEX title_idx
          ON films (title);
  </ProgramListing>
  <para>
   To create a rtree index on a point attribute so that we
   can efficiently use box operators on the result of the
   conversion function:
  </para>
  <programlisting>
   CREATE INDEX pointloc
          ON points USING RTREE (point2box(location) box_ops);

   SELECT * FROM points
          WHERE point2box(points.pointloc) = boxes.box;
<comment>
Is this example correct?
</comment>
  </programlisting>
 </REFSECT1>
 
 <REFSECT1 ID="R1-SQL-CREATEINDEX-3">
  <TITLE>
   Compatibility
  </TITLE>
  <PARA>
  </PARA>
  
  <REFSECT2 ID="R2-SQL-CREATEINDEX-4">
   <REFSECT2INFO>
    <DATE>1998-04-15</DATE>
   </REFSECT2INFO>
   <TITLE>
    SQL92
   </TITLE>
   <PARA>
    CREATE INDEX is a PostgreSQL language extension.
   </PARA>
   <para>
    There is no CREATE INDEX command in SQL92.
   </para>
  </refsect2>
 </refsect1>
</REFENTRY>


<!-- Keep this comment at the end of the file
Local variables:
mode: sgml
sgml-omittag:t
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"../reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:"/usr/lib/sgml/catalog"
sgml-local-ecat-files:nil
End:
-->
