DELETE v16
Name
DELETE — Delete rows of a table.
Synopsis
DELETE [ <optimizer_hint> ] [ FROM ] <table>[@<dblink> ]
[ WHERE <condition> ]
[ RETURNING <return_expression> [, ...]
{ INTO { <record> | <variable> [, ...] }
| BULK COLLECT INTO <collection> [, ...] } ]Description
DELETE deletes rows that satisfy the WHERE clause from the specified table. Omitting the WHERE clause deletes all rows in the table, leaving an empty table. You need the DELETE privilege on the table to delete from it. You also need the SELECT privilege for any table whose values are read in the condition.
The FROM keyword is optional if EDB Postgres Advanced Server is installed in Oracle-compatible mode. It's required if EDB Postgres Advanced Server is installed in Postgres mode.
Note
The TRUNCATE command is a faster way to remove all rows from a table.
You can specify the RETURNING INTO { record | variable [, ...] } clause only if you use the DELETE command in an SPL program. The result set of the DELETE command can't include more than one row. If it does, an error occurs. If the result set is empty, then the contents of the target record or variables are set to null.
You can specify the RETURNING BULK COLLECT INTO collection [, ...] clause only if you use the DELETE command in an SPL program. If you specify more than one collection as the target of the BULK COLLECT INTO clause, then each collection must consist of a single, scalar field. That is, collection can't be a record.
The result set of the DELETE command can contain zero, one, or more rows. The return_expression evaluated for each row of the result set becomes an element in collection, starting with the first element. Any existing rows in collection are deleted. If the result set is empty, then collection is empty.
Parameters
optimizer_hint
Comment-embedded hints to the optimizer for selecting an execution plan.
table
The name (optionally schema-qualified) of an existing table.
dblink
Database link name identifying a remote database. For more information, see CREATE DATABASE LINK.
condition
A value expression that returns a value of type BOOLEAN that determines the rows to delete.
return_expression
An expression that can include one or more columns from table. If you specify a column name from table in return_expression, the value substituted for the column when return_expression is evaluated is the value from the deleted row.
record
A record whose field to assign the evaluated return_expression. The first return_expression is assigned to the first field in record, the second return_expression is assigned to the second field in record, and so on. The number of fields in record must exactly match the number of expressions, and the fields must be type-compatible with their assigned expressions.
variable
A variable to which to assign the evaluated return_expression. If you specify more than one return_expression and variable, the first return_expression is assigned to the first variable, the second return_expression is assigned to the second variable, and so on. The number of variables specified following the INTO keyword must exactly match the number of expressions following the RETURNING keyword, and the variables must be type-compatible with their assigned expressions.
collection
A collection in which an element is created from the evaluated return_expression. You can have a single collection, which can be a collection of a single field or a collection of a record type. Alternatively, you can have more than one collection. In that case, each collection must consist of a single field. The number of return expressions must match in number and order the number of fields in all specified collections. Each corresponding return_expression and collection field must be type-compatible.
Examples
Delete all rows for employee 7900 from the jobhist table:
DELETE FROM jobhist WHERE empno = 7900;
Clear the table jobhist:
DELETE FROM jobhist;