Home Explore Blog CI



postgresql

doc/src/sgml/ref/drop_index.sgml
e23700c40684911dd667e111ce7db92f1d9cd900c9e9cad80000000300000f1c
<!--
doc/src/sgml/ref/drop_index.sgml
PostgreSQL documentation
-->

<refentry id="sql-dropindex">
 <indexterm zone="sql-dropindex">
  <primary>DROP INDEX</primary>
 </indexterm>

 <refmeta>
  <refentrytitle>DROP INDEX</refentrytitle>
  <manvolnum>7</manvolnum>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>

 <refnamediv>
  <refname>DROP INDEX</refname>
  <refpurpose>remove an index</refpurpose>
 </refnamediv>

 <refsynopsisdiv>
<synopsis>
DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] <replaceable class="parameter">name</replaceable> [, ...] [ CASCADE | RESTRICT ]
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

  <para>
   <command>DROP INDEX</command> drops an existing index from the database
   system. To execute this command you must be the owner of
   the index.
  </para>
 </refsect1>

 <refsect1>
  <title>Parameters</title>

  <variablelist>
   <varlistentry>
    <term><literal>CONCURRENTLY</literal></term>
    <listitem>
     <para>
      Drop the index without locking out concurrent selects, inserts, updates,
      and deletes on the index's table.  A normal <command>DROP INDEX</command>
      acquires an <literal>ACCESS EXCLUSIVE</literal> lock on the table,
      blocking other accesses until the index drop can be completed.  With
      this option, the command instead waits until conflicting transactions
      have completed.
     </para>
     <para>
      There are several caveats to be aware of when using this option.
      Only one index name can be specified, and the <literal>CASCADE</literal> option
      is not supported.  (Thus, an index that supports a <literal>UNIQUE</literal> or
      <literal>PRIMARY KEY</literal> constraint cannot be dropped this way.)
      Also, regular <command>DROP INDEX</command> commands can be
      performed within a transaction block, but
      <command>DROP INDEX CONCURRENTLY</command> cannot.
      Lastly, indexes on partitioned tables cannot be dropped using this
      option.
     </para>
     <para>
      For temporary tables, <command>DROP INDEX</command> is always
      non-concurrent, as no other session can access them, and
      non-concurrent index drop is cheaper.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>IF EXISTS</literal></term>
    <listitem>
     <para>
      Do not throw an error if the index does not exist. A notice is issued
      in this case.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">name</replaceable></term>
    <listitem>
     <para>
      The name (optionally schema-qualified) of an index to remove.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>CASCADE</literal></term>
    <listitem>
     <para>
      Automatically drop objects that depend on the index,
      and in turn all objects that depend on those objects
      (see <xref linkend="ddl-depend"/>).
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>RESTRICT</literal></term>
    <listitem>
     <para>
      Refuse to drop the index if any objects depend on it.  This is
      the default.
     </para>
    </listitem>
   </varlistentry>
  </variablelist>
 </refsect1>

 <refsect1>
  <title>Examples</title>

  <para>
   This command will remove the index <literal>title_idx</literal>:

<programlisting>
DROP INDEX title_idx;
</programlisting></para>
 </refsect1>

 <refsect1>
  <title>Compatibility</title>

  <para>
   <command>DROP 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-createindex"/></member>
  </simplelist>
 </refsect1>

</refentry>

Chunks
55ffc267 (1st chunk of `doc/src/sgml/ref/drop_index.sgml`)
Title: DROP INDEX
Summary
This documentation describes the DROP INDEX command in PostgreSQL, which removes an existing index. The command syntax and parameters, including options for concurrent dropping and handling dependencies, are explained. The user must be the owner of the index to execute the command. The command is a PostgreSQL extension and not part of the SQL standard.