doc/src/sgml/ref/drop_index.sgml

   1 <!--
   2 doc/src/sgml/ref/drop_index.sgml
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="sql-dropindex">
   7  <indexterm zone="sql-dropindex">
   8   <primary>DROP INDEX</primary>
   9  </indexterm>
  10
  11  <refmeta>
  12   <refentrytitle>DROP INDEX</refentrytitle>
  13   <manvolnum>7</manvolnum>
  14   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  15  </refmeta>
  16
  17  <refnamediv>
  18   <refname>DROP INDEX</refname>
  19   <refpurpose>remove an index</refpurpose>
  20  </refnamediv>
  21
  22  <refsynopsisdiv>
  23 <synopsis>
  24 DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] <replaceable class="parameter">name</replaceable> [, ...] [ CASCADE | RESTRICT ]
  25 </synopsis>
  26  </refsynopsisdiv>
  27
  28  <refsect1>
  29   <title>Description</title>
  30
  31   <para>
  32    <command>DROP INDEX</command> drops an existing index from the database
  33    system. To execute this command you must be the owner of
  34    the index.
  35   </para>
  36  </refsect1>
  37
  38  <refsect1>
  39   <title>Parameters</title>
  40
  41   <variablelist>
  42    <varlistentry>
  43     <term><literal>CONCURRENTLY</literal></term>
  44     <listitem>
  45      <para>
  46       Drop the index without locking out concurrent selects, inserts, updates,
  47       and deletes on the index's table.  A normal <command>DROP INDEX</command>
  48       acquires an <literal>ACCESS EXCLUSIVE</literal> lock on the table,
  49       blocking other accesses until the index drop can be completed.  With
  50       this option, the command instead waits until conflicting transactions
  51       have completed.
  52      </para>
  53      <para>
  54       There are several caveats to be aware of when using this option.
  55       Only one index name can be specified, and the <literal>CASCADE</literal> option
  56       is not supported.  (Thus, an index that supports a <literal>UNIQUE</literal> or
  57       <literal>PRIMARY KEY</literal> constraint cannot be dropped this way.)
  58       Also, regular <command>DROP INDEX</command> commands can be
  59       performed within a transaction block, but
  60       <command>DROP INDEX CONCURRENTLY</command> cannot.
  61       Lastly, indexes on partitioned tables cannot be dropped using this
  62       option.
  63      </para>
  64      <para>
  65       For temporary tables, <command>DROP INDEX</command> is always
  66       non-concurrent, as no other session can access them, and
  67       non-concurrent index drop is cheaper.
  68      </para>
  69     </listitem>
  70    </varlistentry>
  71
  72    <varlistentry>
  73     <term><literal>IF EXISTS</literal></term>
  74     <listitem>
  75      <para>
  76       Do not throw an error if the index does not exist. A notice is issued
  77       in this case.
  78      </para>
  79     </listitem>
  80    </varlistentry>
  81
  82    <varlistentry>
  83     <term><replaceable class="parameter">name</replaceable></term>
  84     <listitem>
  85      <para>
  86       The name (optionally schema-qualified) of an index to remove.
  87      </para>
  88     </listitem>
  89    </varlistentry>
  90
  91    <varlistentry>
  92     <term><literal>CASCADE</literal></term>
  93     <listitem>
  94      <para>
  95       Automatically drop objects that depend on the index,
  96       and in turn all objects that depend on those objects
  97       (see <xref linkend="ddl-depend"/>).
  98      </para>
  99     </listitem>
 100    </varlistentry>
 101
 102    <varlistentry>
 103     <term><literal>RESTRICT</literal></term>
 104     <listitem>
 105      <para>
 106       Refuse to drop the index if any objects depend on it.  This is
 107       the default.
 108      </para>
 109     </listitem>
 110    </varlistentry>
 111   </variablelist>
 112  </refsect1>
 113
 114  <refsect1>
 115   <title>Examples</title>
 116
 117   <para>
 118    This command will remove the index <literal>title_idx</literal>:
 119
 120 <programlisting>
 121 DROP INDEX title_idx;
 122 </programlisting></para>
 123  </refsect1>
 124
 125  <refsect1>
 126   <title>Compatibility</title>
 127
 128   <para>
 129    <command>DROP INDEX</command> is a
 130    <productname>PostgreSQL</productname> language extension.  There
 131    are no provisions for indexes in the SQL standard.
 132   </para>
 133  </refsect1>
 134
 135  <refsect1>
 136   <title>See Also</title>
 137
 138   <simplelist type="inline">
 139    <member><xref linkend="sql-createindex"/></member>
 140   </simplelist>
 141  </refsect1>
 142
 143 </refentry>