PostgreSQL 8.2.22 Documentation
Prev | Fast Backward |  | Fast Forward | Next

# DELETE

## Name

DELETE -- delete rows of a table

## Synopsis

    DELETE FROM [ ONLY ] table [ [ AS ] alias ]
        [ USING usinglist ]
        [ WHERE condition ]
        [ RETURNING * | output_expression [ AS output_name ] [, ...] ]

## Description

`DELETE` deletes rows that satisfy the `WHERE` clause from the specified table. If the `WHERE` clause is absent, the effect is to delete all rows in the table. The result is a valid, but empty table.

> Tip: TRUNCATE is a PostgreSQL extension that provides a faster mechanism to remove all rows from a table.

By default, `DELETE` will delete rows in the specified table and all its child tables. If you wish to delete only from the specific table mentioned, you must use the `ONLY` clause.

There are two ways to delete rows in a table using information contained in other tables in the database: using sub-selects, or specifying additional tables in the `USING` clause. Which technique is more appropriate depends on the specific circumstances.

The optional `RETURNING` clause causes `DELETE` to compute and return value(s) based on each row actually deleted. Any expression using the table's columns, and/or columns of other tables mentioned in `USING`, can be computed. The syntax of the `RETURNING` list is identical to that of the output list of `SELECT`.

You must have the `DELETE` privilege on the table to delete from it, as well as the `SELECT` privilege for any table in the `USING` clause or whose values are read in the `condition`.

## Parameters

`ONLY`

  If specified, delete rows from the named table only. When not specified, any tables inheriting from the named table are also processed.

`table`

  The name (optionally schema-qualified) of an existing table.

`alias`

  A substitute name for the target table. When an alias is provided, it completely hides the actual name of the table. For example, given `DELETE FROM foo AS f`, the remainder of the `DELETE` statement must refer to this table as `f` not `foo`.

`usinglist`

  A list of table expressions, allowing columns from other tables to appear in the `WHERE` condition. This is similar to the list of tables that can be specified in the FROM Clause of a `SELECT` statement; for example, an alias for the table name can be specified. Do not repeat the target table in the `usinglist`, unless you wish to set up a self-join.

`condition`

  An expression returning a value of type `boolean`, which determines the rows that are to be deleted.

`output_expression`

  An expression to be computed and returned by the `DELETE` command after each row is deleted. The expression may use any column names of the `table` or table(s) listed in `USING`. Write `*` to return all columns.

`output_name`

  A name to use for a returned column.

## Outputs

On successful completion, a `DELETE` command returns a command tag of the form

    DELETE count

The `count` is the number of rows deleted. If `count` is 0, no rows matched the `condition` (this is not considered an error).

If the `DELETE` command contains a `RETURNING` clause, the result will be similar to that of a `SELECT` statement containing the columns and values defined in the `RETURNING` list, computed over the row(s) deleted by the command.

## Notes

PostgreSQL lets you reference columns of other tables in the `WHERE` condition by specifying the other tables in the `USING` clause. For example, to delete all films produced by a given producer, one might do

    DELETE FROM films USING producers
      WHERE producer_id = producers.id AND producers.name = 'foo';

What is essentially happening here is a join between `films` and `producers`, with all successfully joined `films` rows being marked for deletion. This syntax is not standard. A more standard way to do it is

    DELETE FROM films
      WHERE producer_id IN (SELECT id FROM producers WHERE name = 'foo');

In some cases the join style is easier to write or faster to execute than the sub-select style.

## Examples

Delete all films but musicals:

    DELETE FROM films WHERE kind <> 'Musical';

Clear the table `films`:

    DELETE FROM films;

Delete completed tasks, returning full details of the deleted rows:

    DELETE FROM tasks WHERE status = 'DONE' RETURNING *;

## Compatibility

This command conforms to the SQL standard, except that the `USING` and `RETURNING` clauses are PostgreSQL extensions.

Prev | Home | Next
DECLARE | Up | DROP AGGREGATE