<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title> Practical PostgreSQL</title>
<meta name="keywords" content="postgresql, support, recovery, design, software, hosting, expterise, programming, postgres, pgsql, replication, mysql, firebird, database, sql, free, open source, software" />
<meta name="description" content="Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. " />
<link rel="shortcut icon" href="../favicon.ico" type="image/x-icon" />
<link rel="stylesheet" type="text/css" href="../css/layout.css" />
<link rel="stylesheet" type="text/css" href="../css/cmd4.css" />
<script language="javascript" type="text/javascript"><!--
greymenuhomewideLit = new Image();
greymenuhomewideLit.src = "/images/greymenu_homewide_lit.gif";
greymenuaboutLit = new Image();
greymenuaboutLit.src = "/images/greymenu_about_lit.gif";
greymenuproductsLit = new Image();
greymenuproductsLit.src = "/images/greymenu_products_lit.gif";
greymenuservicesLit = new Image();
greymenuservicesLit.src = "/images/greymenu_services_lit.gif";
greymenusupportLit = new Image();
greymenusupportLit.src = "/images/greymenu_support_lit.gif";
greymenucommunityLit = new Image();
greymenucommunityLit.src = "/images/greymenu_community_lit.gif";
//--></script>
</head>
<body>
<div align="center">
<div id="widecontainer">
<div id="widetop">
<div class="throwleft" style="width: 300px; text-align: left;"><a href="http://www.commandprompt.com/"><img src="../images/cmd4logo_wide.gif" border="0" alt="Command Prompt, Inc." /></a></div>
<div class="throwright" style="width: 400px; text-align: right;">
<div id="topright">
<form action="index.html" name="cmdform" method="post">
<ul>
<li><input type="text" name="search" value="" /></li>
<li><input type="submit" name="action" value="Search Book" /></li>
</ul>
</form>
</div>
</div>
</div>
<ul id="greymenu_wide">
<li><a href="http://www.commandprompt.com/home/"><img onmouseover="this.src = greymenuhomewideLit.src;" onmouseout="this.src = '/images/greymenu_homewide.gif';" src="../images/greymenu_homewide.gif" border="0" height="22" title="" alt="" /></a></li><li><img src="../images/greymenu_divider.gif" border="0" width="2" height="22" alt="|" /></li><li><a href="http://www.commandprompt.com/about/"><img onmouseover="this.src = greymenuaboutLit.src;" onmouseout="this.src = '/images/greymenu_about.gif';" src="../images/greymenu_about.gif" border="0" height="22" title="" alt="" /></a></li><li><img src="../images/greymenu_divider.gif" border="0" width="2" height="22" alt="|" /></li><li><a href="http://www.commandprompt.com/products/"><img onmouseover="this.src = greymenuproductsLit.src;" onmouseout="this.src = '/images/greymenu_products.gif';" src="../images/greymenu_products.gif" border="0" height="22" title="" alt="" /></a></li><li><img src="../images/greymenu_divider.gif" border="0" width="2" height="22" alt="|" /></li><li><a href="http://www.commandprompt.com/services/"><img onmouseover="this.src = greymenuservicesLit.src;" onmouseout="this.src = '/images/greymenu_services.gif';" src="../images/greymenu_services.gif" border="0" height="22" title="" alt="" /></a></li><li><img src="../images/greymenu_divider.gif" border="0" width="2" height="22" alt="|" /></li><li><a href="http://www.commandprompt.com/support/"><img onmouseover="this.src = greymenusupportLit.src;" onmouseout="this.src = '/images/greymenu_support.gif';" src="../images/greymenu_support.gif" border="0" height="22" title="" alt="" /></a></li><li><img src="../images/greymenu_divider.gif" border="0" width="2" height="22" alt="|" /></li><li><a href="http://www.commandprompt.com/community/"><img onmouseover="this.src = greymenucommunityLit.src;" onmouseout="this.src = '/images/greymenu_community.gif';" src="../images/greymenu_community.gif" border="0" height="22" title="" alt="" /></a></li> </ul>
<div style="padding-left: 16px; width: 96%;">
<HTML
><HEAD
><TITLE
>CREATE INDEX</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.72
"><LINK
REL="HOME"
TITLE="Practical PostgreSQL"
href="book1"><LINK
REL="UP"
TITLE="PostgreSQL Command Reference"
href="c22759"><LINK
REL="PREVIOUS"
TITLE="CREATE GROUP"
href="r24172"><LINK
REL="NEXT"
TITLE="CREATE LANGUAGE"
href="r24435"></HEAD
><BODY
CLASS="REFENTRY"
BGCOLOR="#FFFFFF"
TEXT="#000000"
LINK="#0000FF"
VLINK="#840084"
ALINK="#0000FF"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="3"
ALIGN="center"
>Practical PostgreSQL</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="bottom"
><A
href="r24172"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="80%"
ALIGN="center"
VALIGN="bottom"
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="bottom"
><A
href="r24435"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><H1
><A
NAME="SQL-CREATEINDEX"
>CREATE INDEX</A
></H1
><DIV
CLASS="REFNAMEDIV"
><A
NAME="AEN24258"
></A
><H2
>Name</H2
>CREATE INDEX -- Places an index on a table.</DIV
><DIV
CLASS="REFSYNOPSISDIV"
><A
NAME="AEN24261"
></A
><H2
>Synopsis</H2
><PRE
CLASS="SYNOPSIS"
>CREATE [ UNIQUE ] INDEX <TT
CLASS="REPLACEABLE"
><I
>index_name</I
></TT
> ON <TT
CLASS="REPLACEABLE"
><I
>table</I
></TT
>
[ USING <TT
CLASS="REPLACEABLE"
><I
>method</I
></TT
> ] ( <TT
CLASS="REPLACEABLE"
><I
>column</I
></TT
> [ <TT
CLASS="REPLACEABLE"
><I
>op_class</I
></TT
> ] [, ...] )
CREATE [ UNIQUE ] INDEX <TT
CLASS="REPLACEABLE"
><I
>index_name</I
></TT
> ON <TT
CLASS="REPLACEABLE"
><I
>table</I
></TT
>
[ USING <TT
CLASS="REPLACEABLE"
><I
>method</I
></TT
> ] ( <TT
CLASS="REPLACEABLE"
><I
>func_name</I
></TT
> ( <TT
CLASS="REPLACEABLE"
><I
>column</I
></TT
> [, ... ] ) [ <TT
CLASS="REPLACEABLE"
><I
>op_class</I
></TT
> ] )</PRE
><DIV
CLASS="REFSECT2"
><A
NAME="R2-SQL-CREATEINDEX-1"
></A
><H3
>Parameters</H3
><P
> <P
></P
><DIV
CLASS="VARIABLELIST"
><DL
><DT
><TT
CLASS="LITERAL"
>UNIQUE</TT
></DT
><DD
><P
>The optional <TT
CLASS="LITERAL"
>UNIQUE</TT
> keyword. When used, this causes the database to check for, and prevent, duplicate values within the column (or combined columns) it is placed upon. This check will occur both when the index is created and each time data is added to the table. PostgreSQL will then generate an error whenever an <TT
CLASS="LITERAL"
>INSERT</TT
> or <TT
CLASS="LITERAL"
>UPDATE</TT
> request is made that would place duplicate data within the index, and the command will fail.</P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>index_name</I
></TT
></DT
><DD
><P
>The name for the new index.</P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>table</I
></TT
></DT
><DD
><P
>The name of the table you are placing the index on.</P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>method</I
></TT
></DT
><DD
><P
>The type of indexing method you wish to use for the index. There are three methods available to choose from, the default being <TT
CLASS="LITERAL"
>btree</TT
>:
<P
></P
><DIV
CLASS="VARIABLELIST"
><DL
><DT
><TT
CLASS="LITERAL"
>btree</TT
></DT
><DD
><P
>The PostgreSQL implementation of Lehman-Yao high-concurrency B-trees.</P
></DD
><DT
><TT
CLASS="LITERAL"
>rtree</TT
></DT
><DD
><P
>The PostgreSQL implementation of standard R-trees using Guttman's quadratic split algorithm.</P
></DD
><DT
><TT
CLASS="LITERAL"
>hash</TT
></DT
><DD
><P
>The PostgreSQL implementation of Litwin's linear hashing.</P
></DD
></DL
></DIV
>
</P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>column</I
></TT
></DT
><DD
><P
>The name of the column (or comma-delimited list of columns) on which to place the index.</P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>op_class</I
></TT
></DT
><DD
><P
>The optionally specified associated operator class. For most users, this should not be specified.</P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>func_name</I
></TT
></DT
><DD
><P
>The name of a function you wish <TT
CLASS="LITERAL"
>CREATE INDEX</TT
> to use on the specified columns (rather than on the data values literally in those columns). The specified function must return a valid value that can be indexed (e.g., not a set of values).</P
></DD
></DL
></DIV
>
</P
></DIV
><DIV
CLASS="REFSECT2"
><A
NAME="R2-SQL-CREATEINDEX-2"
></A
><H3
>Results</H3
><P
> <P
></P
><DIV
CLASS="VARIABLELIST"
><DL
><DT
><TT
CLASS="LITERAL"
>CREATE</TT
></DT
><DD
><P
>The message returned when an index is created successfully.</P
></DD
><DT
><TT
CLASS="LITERAL"
>ERROR: Cannot create index: '</TT
><TT
CLASS="REPLACEABLE"
><I
>index_name</I
></TT
><TT
CLASS="LITERAL"
>' already exists</TT
></DT
><DD
><P
>The error returned if an index with the name you specified already exists.</P
></DD
><DT
><TT
CLASS="LITERAL"
>ERROR: DefineIndex: attribute "</TT
><TT
CLASS="REPLACEABLE"
><I
>column</I
></TT
><TT
CLASS="LITERAL"
>" not found</TT
></DT
><DD
><P
>The error returned if the specified <TT
CLASS="REPLACEABLE"
><I
>column</I
></TT
> does not exist in the specified <TT
CLASS="REPLACEABLE"
><I
>table</I
></TT
> to index.</P
></DD
><DT
><TT
CLASS="LITERAL"
>ERROR: DefineIndex: relation "</TT
><TT
CLASS="REPLACEABLE"
><I
>table</I
></TT
><TT
CLASS="LITERAL"
>" not found</TT
></DT
><DD
><P
>The error returned if the specified <TT
CLASS="REPLACEABLE"
><I
>table</I
></TT
> does not exist in the connected database.</P
></DD
></DL
></DIV
>
</P
></DIV
></DIV
><DIV
CLASS="REFSECT1"
><A
NAME="R1-SQL-CREATEINDEX-1"
></A
><H2
>Description</H2
><P
>Use <TT
CLASS="LITERAL"
>CREATE INDEX</TT
> to build an optimization index on a specified table, based on one or more of its
columns. Remember that while indices are designed to improve the performance and effectiveness of your database, using them
on tables whose cost of index maintenance outweighs the practical benefit actually <SPAN
><I
CLASS="EMPHASIS"
>decreases</I
></SPAN
> overall
performance.</P
><DIV
CLASS="REFSECT2"
><A
NAME="AEN24372"
></A
><H3
>Column index</H3
><P
>You may create an index specifying a list of one or more table columns. This is the "traditional" index type. It may be used by queries that directly reference the indexed columns in a <TT
CLASS="LITERAL"
>WHERE</TT
> clause. Note that R-tree and Hash indices may only index one column, though B-tree indices can index up to sixteen columns.</P
></DIV
><DIV
CLASS="REFSECT2"
><A
NAME="AEN24376"
></A
><H3
>Functional index</H3
><P
>An alternate type of index is one called a <SPAN
><I
CLASS="EMPHASIS"
>functional index</I
></SPAN
>. A functional index is an index based on the returned value of a function applied to one or more columns. Such an index is useful for queries that use the same function in a <TT
CLASS="LITERAL"
>WHERE</TT
> clause frequently.</P
><P
>For example, if you have a query that always references <TT
CLASS="LITERAL"
>upper(last_name)</TT
> in its <TT
CLASS="LITERAL"
>WHERE</TT
> clause, you could optimized that query by creating a functional index on <TT
CLASS="LITERAL"
>upper(last_name)</TT
>.</P
></DIV
><DIV
CLASS="REFSECT2"
><A
NAME="AEN24385"
></A
><H3
>Operators and operator classes</H3
><P
>The PostgreSQL query optimizer will use different indices for different operators used in a comparison. It will choose which type of index to used based on the pre-defined list shown in <A
href="r24254#OPERATORSVSINDEX"
>Table 14-1</A
>.</P
><DIV
CLASS="TABLE"
><A
NAME="OPERATORSVSINDEX"
></A
><P
><B
>Table 14-1. Operator/index correspondence</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><THEAD
><TR
><TH
WIDTH="4"
ALIGN="LEFT"
VALIGN="TOP"
>Index</TH
><TH
WIDTH="10"
ALIGN="LEFT"
VALIGN="TOP"
>Operator</TH
></TR
></THEAD
><TBODY
><TR
><TD
WIDTH="4"
ALIGN="LEFT"
VALIGN="TOP"
><P
>B-tree</P
></TD
><TD
WIDTH="10"
ALIGN="LEFT"
VALIGN="TOP"
><P
><TT
CLASS="LITERAL"
><, <=, >=, ></TT
></P
></TD
></TR
><TR
><TD
WIDTH="4"
ALIGN="LEFT"
VALIGN="TOP"
><P
>R-tree</P
></TD
><TD
WIDTH="10"
ALIGN="LEFT"
VALIGN="TOP"
><P
><TT
CLASS="LITERAL"
><<, &<, &>, >>, @, ~=, &&</TT
></P
></TD
></TR
><TR
><TD
WIDTH="4"
ALIGN="LEFT"
VALIGN="TOP"
><P
>Hash</P
></TD
><TD
WIDTH="10"
ALIGN="LEFT"
VALIGN="TOP"
><P
><TT
CLASS="LITERAL"
>=</TT
></P
></TD
></TR
></TBODY
></TABLE
></DIV
><P
>You can optionally specify an <SPAN
><I
CLASS="EMPHASIS"
>operator class</I
></SPAN
> for each column on which an index is placed. This is done by setting the optional <TT
CLASS="LITERAL"
>op_class</TT
> parameter to the class of operator you intend to use. This option only exists because, in some circumstances, there can be more than one meaningful way to order data. The default indexing method is generally sufficient for most users, however, and this option is best left unused unless you are creating your own custom types and operators.</P
></DIV
></DIV
><DIV
CLASS="REFSECT1"
><A
NAME="R1-SQL-CREATEINDEX-2"
></A
><H2
>Examples</H2
><P
>The following example creates a unique index on the <TT
CLASS="LITERAL"
>id</TT
> column of the <TT
CLASS="LITERAL"
>employees</TT
> table:</P
><PRE
CLASS="SCREEN"
>booktown=# <TT
CLASS="USERINPUT"
><B
>CREATE UNIQUE INDEX employee_id_idx</B
></TT
>
booktown-# <TT
CLASS="USERINPUT"
><B
> ON employees (id);</B
></TT
>
CREATE</PRE
><P
>The next example creates a functional index on the <TT
CLASS="LITERAL"
>last_name</TT
> column of the <TT
CLASS="LITERAL"
>employees</TT
> table, using the <TT
CLASS="LITERAL"
>upper()</TT
> function:</P
><PRE
CLASS="SCREEN"
>booktown=# <TT
CLASS="USERINPUT"
><B
>CREATE INDEX employee_upper_name_idx</B
></TT
>
booktown-# <TT
CLASS="USERINPUT"
><B
> ON employees (upper(last_name));</B
></TT
>
CREATE</PRE
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
href="r24172"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
href="book1"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
href="r24435"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>CREATE GROUP</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
href="c22759"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>CREATE LANGUAGE</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>
</div>
<br />
<div id="thefineprint">
<a href="http://www.commandprompt.com/products/mammothpostgresql/"><img src="../images/powered_by_mammoth.gif" alt="Powered by Mammoth PostgreSQL" title="Powered by Mammoth PostgreSQL" border="0" /></a>
<br />
Copyright © 2000-2007 Command Prompt, Inc. All Rights Reserved. All trademarks property of their respective owners.
</div>
</div>
</div>
</body>
</html>