Removing Rows with DELETE
Existing row data within PostgreSQL can be removed with the standard SQL DELETE command. Unless carefully working within transaction blocks, removal via the
DELETE command is permanent, and extreme
caution should therefore be taken before attempting to remove data from your
database.
The syntax to remove one or more rows from a table is as follows:
DELETE FROM [ ONLY ] table [ WHERE condition ]
DELETE FROM [ ONLY ]tableThe
ONLYkeyword may be used to indicate that only the tabletableshould have rows removed from it, and none of its sub-tables. This is only relevant iftableis inherited by any other tables.WHEREconditionThe
WHEREclause describes under whatconditionto delete rows fromtable.If unspecified, all rows in the table will be deleted.
The WHERE clause is almost always part of a DELETE
statement. It specifies which rows in the target table are to be deleted based on its specified
conditions, which may be expressed syntactically in the same form as in the SELECT statement.
It is a good habit to execute a SELECT statement with the intended
WHERE clause for your DELETE statement. This allows you
to review the data to be deleted before the DELETE statement is actually
executed. This technique and a simple DELETE statement are demonstrated in
Example 4-57.
Example 4-57. Deleting rows from a table
booktown=# SELECT * FROM stock booktown-# WHERE stock = 0; isbn | cost | retail | stock ------------+-------+--------+------- 0394800753 | 16.00 | 16.95 | ...