DELETE - Removes rows from a table
SYNOPSIS
DELETE FROM [ ONLY ] table [ WHERE condition ]
INPUTS
table The name of an existing table.
condition
This is an SQL selection query which returns the
rows which are to be deleted.
Refer to the SELECT statement for further descrip
tion of the WHERE clause.
OUTPUTS
DELETE count
Message returned if items are successfully deleted.
The count is the number of rows deleted.
If count is 0, no rows were deleted.
DESCRIPTION
DELETE removes rows which satisfy the WHERE clause from
the specified table.
If the condition (WHERE clause) is absent, the effect is
to delete all rows in the table. The result is a valid,
but empty table.
Tip: TRUNCATE [truncate(l)] is a Postgres extension
which provides a faster mechanism to remove all
rows from a table.
By default DELETE will delete tuples in the table speci
fied and all its sub-tables. If you wish to only update
the specific table mentioned, you should use the ONLY
clause.
You must have write access to the table in order to modify
it, as well as read access to any table whose values are
read in the condition.
USAGE
Remove all films but musicals:
DELETE FROM films WHERE kind <> 'Musical';
SELECT * FROM films;
-------+---------------------------+-----+------------+---------+-------
UA501 | West Side Story | 105 | 1961-01-03 | Musical | 02:32
TC901 | The King and I | 109 | 1956-08-11 | Musical | 02:13
WD101 | Bed Knobs and Broomsticks | 111 | | Musical | 01:57
(3 rows)
Clear the table films:
DELETE FROM films;
SELECT * FROM films;
code | title | did | date_prod | kind | len
------+-------+-----+-----------+------+-----
(0 rows)
COMPATIBILITY
SQL92
SQL92 allows a positioned DELETE statement:
DELETE FROM table WHERE
CURRENT OF cursor
where cursor identifies an open cursor. Interactive cur
sors in Postgres are read-only.
Man(1) output converted with
man2html