Home • Explore • Blog • CI
☰



postgresql
14th chunk of `doc/src/sgml/ref/create_index.sgml`
8d80b891680a9f7c9ae31dd249b98ac7d570a1f629cb36d70000000100000cac
 create a unique B-tree index on the column <literal>title</literal> in
   the table <literal>films</literal>:
<programlisting>
CREATE UNIQUE INDEX title_idx ON films (title);
</programlisting>
  </para>

  <para>
   To create a unique B-tree index on the column <literal>title</literal>
   with included columns <literal>director</literal>
   and <literal>rating</literal> in the table <literal>films</literal>:
<programlisting>
CREATE UNIQUE INDEX title_idx ON films (title) INCLUDE (director, rating);
</programlisting>
  </para>

  <para>
   To create a B-Tree index with deduplication disabled:
<programlisting>
CREATE INDEX title_idx ON films (title) WITH (deduplicate_items = off);
</programlisting>
  </para>

  <para>
   To create an index on the expression <literal>lower(title)</literal>,
   allowing efficient case-insensitive searches:
<programlisting>
CREATE INDEX ON films ((lower(title)));
</programlisting>
   (In this example we have chosen to omit the index name, so the system
   will choose a name, typically <literal>films_lower_idx</literal>.)
  </para>

  <para>
   To create an index with non-default collation:
<programlisting>
CREATE INDEX title_idx_german ON films (title COLLATE "de_DE");
</programlisting>
  </para>

  <para>
   To create an index with non-default sort ordering of nulls:
<programlisting>
CREATE INDEX title_idx_nulls_low ON films (title NULLS FIRST);
</programlisting>
  </para>

  <para>
   To create an index with non-default fill factor:
<programlisting>
CREATE UNIQUE INDEX title_idx ON films (title) WITH (fillfactor = 70);
</programlisting>
  </para>

  <para>
   To create a <acronym>GIN</acronym> index with fast updates disabled:
<programlisting>
CREATE INDEX gin_idx ON documents_table USING GIN (locations) WITH (fastupdate = off);
</programlisting>
  </para>

  <para>
   To create an index on the column <literal>code</literal> in the table
   <literal>films</literal> and have the index reside in the tablespace
   <literal>indexspace</literal>:
<programlisting>
CREATE INDEX code_idx ON films (code) TABLESPACE indexspace;
</programlisting>
  </para>

  <para>
   To create a GiST index on a point attribute so that we
   can efficiently use box operators on the result of the
   conversion function:
<programlisting>
CREATE INDEX pointloc
    ON points USING gist (box(location,location));
SELECT * FROM points
    WHERE box(location,location) &amp;&amp; '(0,0),(1,1)'::box;
</programlisting>
  </para>

  <para>
   To create an index without locking out writes to the table:
<programlisting>
CREATE INDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity);
</programlisting></para>

 </refsect1>

 <refsect1>
  <title>Compatibility</title>

  <para>
   <command>CREATE INDEX</command> is a
   <productname>PostgreSQL</productname> language extension.  There
   are no provisions for indexes in the SQL standard.
  </para>
 </refsect1>

 <refsect1>
  <title>See Also</title>

  <simplelist type="inline">
   <member><xref linkend="sql-alterindex"/></member>
   <member><xref linkend="sql-dropindex"/></member>
   <member><xref linkend="sql-reindex"/></member>
   <member><xref linkend="create-index-progress-reporting"/></member>
  </simplelist>
 </refsect1>
</refentry>
Title: CREATE INDEX: Examples and Compatibility
Summary
This section provides examples of how to use the `CREATE INDEX` command in PostgreSQL, including creating unique B-tree indexes, indexes on expressions, indexes with non-default collations and null sort ordering, indexes with non-default fill factors, GIN indexes with fast updates disabled, indexes residing in specific tablespaces, and indexes that don't lock out writes to the table. The command is a PostgreSQL language extension and not part of the SQL standard. Related commands include `ALTER INDEX`, `DROP INDEX`, and `REINDEX`.