next up previous contents index
Next: DROP AGGREGATE Up: Reference Manual Previous: DECLARE

Subsections

  
DELETE

Name

DELETE -- Removes rows from a table

DELETE FROM 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 description 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 is a Postgres extension which provides a faster mechanism to remove all rows from a table.

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;
          code |            title          | did | date_prod  |   kind  | len 
        -------+---------------------------+-----+------------+---------+------- 
         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 cursors in Postgres are read-only.  


next up previous contents index
Next: DROP AGGREGATE Up: Reference Manual Previous: DECLARE
Bruce Momjian
2001-05-09