1 <REFENTRY ID="SQL-CREATEINDEX">
6 <REFMISCINFO>SQL - Language Statements</REFMISCINFO>
13 Constructs a secondary index
18 <DATE>1998-09-09</DATE>
21 CREATE [ UNIQUE ] INDEX <replaceable class="parameter">index_name</replaceable>
22 ON <replaceable class="parameter">table</replaceable> [ USING <replaceable class="parameter">acc_name</replaceable> ]
23 ( <replaceable class="parameter">column</replaceable> [ <replaceable class="parameter">ops_name</replaceable>] [, ...] )
24 CREATE [ UNIQUE ] INDEX <replaceable class="parameter">index_name</replaceable>
25 ON <replaceable class="parameter">table</replaceable> [ USING <replaceable class="parameter">acc_name</replaceable> ]
26 ( <replaceable class="parameter">func_name</replaceable>( <replaceable class="parameter">column</replaceable> [, ... ]) <replaceable class="parameter">ops_name</replaceable> )
29 <REFSECT2 ID="R2-SQL-CREATEINDEX-1">
31 <DATE>1998-09-09</DATE>
44 Causes the system to check for
45 duplicate values in the table when the index is created (if data
46 already exist) and each time data is added. Attempts to
47 insert or update non-duplicate data will generate an
54 <replaceable class="parameter">index_name</replaceable>
58 The name of the index to be created.
64 <replaceable class="parameter">table</replaceable>
68 The name of the table to be indexed.
74 <replaceable class="parameter">acc_name</replaceable>
78 the name of the access method which is to be used for
79 the index. The default access method is BTREE.
80 Postgres provides three access methods for secondary indexes:
86 an implementation of the Lehman-Yao
87 high-concurrency btrees.
94 <para>implements standard rtrees using Guttman's
95 quadratic split algorithm.
103 an implementation of Litwin's linear hashing.
113 <replaceable class="parameter">column</replaceable>
117 The name of a column of the table.
123 <replaceable class="parameter">ops_name</replaceable>
127 An associated operator class.
128 The following select list returns all ops_names:
131 SELECT am.amname AS acc_name,
132 opc.opcname AS ops_name,
133 opr.oprname AS ops_comp
134 FROM pg_am am, pg_amop amop,
135 pg_opclass opc, pg_operator opr
136 WHERE amop.amopid = am.oid AND
137 amop.amopclaid = opc.oid AND
138 amop.amopopr = opr.oid
139 ORDER BY acc_name, ops_name, ops_comp
147 <replaceable class="parameter">func_name</replaceable>
151 A user-defined function, which returns a value that can
160 <REFSECT2 ID="R2-SQL-CREATEINDEX-2">
162 <DATE>1998-09-09</DATE>
172 <ReturnValue>CREATE</ReturnValue>
176 The message returned if the index is successfully created.
183 <ReturnValue>ERROR: Cannot create index: 'index_name' already exists.</ReturnValue>
187 This error occurs if it is impossible to create the index.
196 <REFSECT1 ID="R1-SQL-CREATEINDEX-1">
198 <DATE>1998-09-09</DATE>
204 <command>CREATE INDEX</command> constructs an index
205 <replaceable class="parameter">index_name</replaceable>.
207 <replaceable class="parameter">table</replaceable>.
211 Indexes are primarily used to enhance database performance.
212 But inappropriate use will result in slower performance.
217 In the first syntax shown above, the key fields for the
218 index are specified as column names; a column may also have
219 an associated operator class. An operator class is used
220 to specify the operators to be used for a particular
221 index. For example, a btree index on four-byte integers
222 would use the <literal>int4_ops</literal> class;
223 this operator class includes
224 comparison functions for four-byte integers. The default
225 operator class is the appropriate operator class for that
229 In the second syntax, an index is defined
230 on the result of a user-defined function
231 <replaceable class="parameter">func_name</replaceable> applied
232 to one or more attributes of a single class. These functional
233 indexes can be used to obtain fast access to data
234 based on operators that would normally require some
235 transformation to apply them to the base data.
238 <REFSECT2 ID="R2-SQL-CREATEINDEX-3">
240 <DATE>1998-09-09</DATE>
246 Currently, only the BTREE access method supports multi-column
247 indexes. Up to 7 keys may be specified.
250 Use <command>DROP INDEX</command>
256 <REFSECT1 ID="R1-SQL-CREATEINDEX-2">
260 <PARA>To create a btree index on the field <literal>title</literal>
261 in the table <literal>films</literal>:
264 CREATE UNIQUE INDEX title_idx
270 Is this example correct?
273 To create a rtree index on a point attribute so that we
274 can efficiently use box operators on the result of the
278 CREATE INDEX pointloc
279 ON points USING RTREE (point2box(location) box_ops);
281 WHERE point2box(points.pointloc) = boxes.box;
287 <REFSECT1 ID="R1-SQL-CREATEINDEX-3">
294 <REFSECT2 ID="R2-SQL-CREATEINDEX-4">
296 <DATE>1998-09-09</DATE>
302 CREATE INDEX is a <productname>Postgres</productname> language extension.
305 There is no <command>CREATE INDEX</command> command in SQL92.
312 <!-- Keep this comment at the end of the file
317 sgml-minimize-attributes:nil
318 sgml-always-quote-attributes:t
321 sgml-parent-document:nil
322 sgml-default-dtd-file:"../reference.ced"
323 sgml-exposed-tags:nil
324 sgml-local-catalogs:"/usr/lib/sgml/catalog"
325 sgml-local-ecat-files:nil