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 ]
table
-
The ONLY keyword may be used to indicate that only the table
table
should have rows removed from it, and none of its sub-tables. This is only relevant if
table
is inherited by any other tables.
-
WHERE
condition
-
The WHERE clause describes under what
condition
to delete rows from
table
. 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 | 0
0394900014 | 23.00 | 23.95 | 0
0451198492 | 36.00 | 46.95 | 0
0451457994 | 17.00 | 22.95 | 0
(4 rows)
booktown=#
DELETE FROM stock
booktown-#
WHERE stock = 0;
DELETE 4
If a WHERE condition is not specified, the DELETE
command removes
all rows
within that table, as shown in Example 4-58.
Example 4-58. Deleting all table rows
booktown=#
DELETE FROM stock_backup;
DELETE 16
