docs/sql-stmts/delete.md - cloudberry-site - Git at Google

 ---
 title: DELETE
 ---

 # DELETE

 Deletes rows from a table.

 ## Synopsis

 ```sql
 [ WITH [ RECURSIVE ] <with_query> [, ...] ]
 DELETE FROM [ONLY] <table_name> [[AS] <alias>]
       [USING <from_item> [, ...] ]
       [WHERE <condition> | WHERE CURRENT OF <cursor_name>]
       [RETURNING * | <output_expression> [[AS] <output_name>] [, …]]
 ```

 ## Description

 `DELETE` deletes rows that satisfy the `WHERE` clause from the specified table. If the `WHERE` clause is absent, the effect is to delete all rows in the table. The result is a valid, but empty table.

 **Tip:** [TRUNCATE](/docs/sql-stmts/truncate.md) provides a faster mechanism to remove all rows from a table.

 There are two ways to delete rows in a table using information contained in other tables in the database: using sub-selects, or specifying additional tables in the `USING` clause. Which technique is more appropriate depends on the specific circumstances.

 The optional `RETURNING` clause causes `DELETE` to compute and return value(s) based on each row actually deleted. Any expression using the table's columns, and/or columns of other tables mentioned in `USING`, can be computed. The syntax of the `RETURNING` list is identical to that of the output list of `SELECT`.

 You must have the `DELETE` privilege on the table to delete from it, as well as the `SELECT` privilege for any table in the `USING` clause or whose values are read in the condition.

 > **Note** The `RETURNING` clause is not supported when deleting from append-optimized tables.

 > **Note** As the default, Apache Cloudberry acquires an `EXCLUSIVE` lock on tables for `DELETE` operations on heap tables. When the Global Deadlock Detector is enabled, the lock mode for `DELETE` operations on heap tables is `ROW EXCLUSIVE`.

 ## Parameters

 **`with_query`**

 The `WITH` clause allows you to specify one or more subqueries that can be referenced by name in the `DELETE` query.

 For a `DELETE` command that includes a `WITH` clause, the clause can only contain `SELECT` statements, the `WITH` clause cannot contain a data-modifying command (`INSERT`, `UPDATE`, or `DELETE`).

 See [SELECT](/docs/sql-stmts/select.md) for details.

 **`table_name`**

 The name (optionally schema-qualified) of the table to delete rows from. If you specify `ONLY` before the table name, Apache Cloudberry deletes matching rows from the named table only. If `ONLY` is not specified, matching rows are also deleted from any tables inheriting from the named table. Optionally, you can specify `*` after the table name to explicitly indicate that descendant tables are included.

 **`alias`**

 A substitute name for the target table. When an alias is provided, it completely hides the actual name of the table. For example, given `DELETE FROM foo AS f`, the remainder of the `DELETE` statement must refer to this table as `f` not `foo`.

 **`from_item`**

 A table expression allowing columns from other tables to appear in the `WHERE` condition. This uses the same syntax as the `FROM` clause of a [SELECT](/docs/sql-stmts/select.md) statement; for example, you can specify an alias for the table name. Do not repeat the target table in the from_item, unless you wish to set up a self-join (in which case it must appear with an alias in the from_item).

 **`condition`**

 An expression that returns a value of type `boolean`. Apache Cloudberry deletes only those rows for which this expression returns `true`.

 **`cursor_name`**

 The name of the cursor to use in a `WHERE CURRENT OF` condition. The row to be deleted is the one most recently fetched from this cursor. The cursor must be a non-grouping query on the `DELETE`'s target table. Note that `WHERE CURRENT OF` cannot be specified together with a Boolean condition. See [DECLARE](/docs/sql-stmts/declare.md) for more information about using cursors with `WHERE CURRENT OF`.

 The `DELETE...WHERE CURRENT OF` cursor statement can only be run on the server, for example in an interactive psql session or a script. Language extensions such as PL/pgSQL do not have support for updatable cursors.

 **`output_expression`**

 An expression to be computed and returned by the `DELETE` command after each row is deleted. The expression can use any column names of the table named by table_name or table(s) listed in `USING`. Write `*` to return all columns.

 **`output_name`**

 A name to use for a returned column.

 ## Outputs

 On successful completion, a `DELETE` command returns a command tag of the form

 ```sql
 DELETE <count>
 ```

 The count is the number of rows deleted. If count is 0, no rows were deleted by the query (this is not considered an error).

 If the `DELETE` command contains a `RETURNING` clause, the result will be similar to that of a `SELECT` statement containing the columns and values defined in the `RETURNING` list, computed over the row(s) deleted by the command.

 ## Notes

 The `RETURNING` clause is not supported when deleting from append-optimized tables.

 The `WHERE CURRENT OF` clause is not supported with replicated tables.

 Apache Cloudberry lets you reference columns of other tables in the `WHERE` condition by specifying the other tables in the `USING` clause. For example, to delete all films produced by a given producer, one can run:

 ```sql
 DELETE FROM films USING producers
   WHERE producer_id = producers.id AND producers.name = 'foo';
 ```
 What is essentially happening here is a join between `films` and `producers`, with all successfully joined `films` rows being marked for deletion. This syntax is not standard. A more standard way to accomplish this is:

 ```sql
 DELETE FROM films
   WHERE producer_id IN (SELECT id FROM producers WHERE name = 'foo');
 ```

 For a partitioned table, all of the child tables are locked during the `DELETE` operation when the Global Deadlock Detector is not enabled (the default). Only some of the leaf child tables are locked when the Global Deadlock Detector is enabled.

 ## Examples

 Delete all films but musicals:

 ```sql
 DELETE FROM films WHERE kind <> 'Musical';
 ```

 Clear the table films:

 ```sql
 DELETE FROM films;
 ```

 Delete completed tasks, returning full details of the deleted rows:

 ```sql
 DELETE FROM tasks WHERE status = 'DONE' RETURNING *;
 ```

 Delete the row of tasks on which the cursor `c_tasks` is currently positioned:

 ```sql
 DELETE FROM tasks WHERE CURRENT OF c_tasks;
 ```

 Delete using a join:

 ```sql
 DELETE FROM rank USING names WHERE names.id = rank.id AND
 name = 'Hannah';
 ```

 ## Compatibility

 This command conforms to the SQL standard, except that the `USING` and `RETURNING` clauses are Apache Cloudberry extensions, as is the ability to use `WITH` with `DELETE`.

 ## See also

 [TRUNCATE](/docs/sql-stmts/truncate.md)
	---
	title: DELETE
	---

	# DELETE

	Deletes rows from a table.

	## Synopsis

	```sql
	[ WITH [ RECURSIVE ] <with_query> [, ...] ]
	DELETE FROM [ONLY] <table_name> [[AS] <alias>]
	[USING <from_item> [, ...] ]
	[WHERE <condition> \| WHERE CURRENT OF <cursor_name>]
	[RETURNING * \| <output_expression> [[AS] <output_name>] [, …]]
	```

	## Description

	`DELETE` deletes rows that satisfy the `WHERE` clause from the specified table. If the `WHERE` clause is absent, the effect is to delete all rows in the table. The result is a valid, but empty table.

	Tip: [TRUNCATE](/docs/sql-stmts/truncate.md) provides a faster mechanism to remove all rows from a table.

	There are two ways to delete rows in a table using information contained in other tables in the database: using sub-selects, or specifying additional tables in the `USING` clause. Which technique is more appropriate depends on the specific circumstances.

	The optional `RETURNING` clause causes `DELETE` to compute and return value(s) based on each row actually deleted. Any expression using the table's columns, and/or columns of other tables mentioned in `USING`, can be computed. The syntax of the `RETURNING` list is identical to that of the output list of `SELECT`.

	You must have the `DELETE` privilege on the table to delete from it, as well as the `SELECT` privilege for any table in the `USING` clause or whose values are read in the condition.

	> Note The `RETURNING` clause is not supported when deleting from append-optimized tables.

	> Note As the default, Apache Cloudberry acquires an `EXCLUSIVE` lock on tables for `DELETE` operations on heap tables. When the Global Deadlock Detector is enabled, the lock mode for `DELETE` operations on heap tables is `ROW EXCLUSIVE`.

	## Parameters

	`with_query`

	The `WITH` clause allows you to specify one or more subqueries that can be referenced by name in the `DELETE` query.

	For a `DELETE` command that includes a `WITH` clause, the clause can only contain `SELECT` statements, the `WITH` clause cannot contain a data-modifying command (`INSERT`, `UPDATE`, or `DELETE`).

	See [SELECT](/docs/sql-stmts/select.md) for details.

	`table_name`

	The name (optionally schema-qualified) of the table to delete rows from. If you specify `ONLY` before the table name, Apache Cloudberry deletes matching rows from the named table only. If `ONLY` is not specified, matching rows are also deleted from any tables inheriting from the named table. Optionally, you can specify `*` after the table name to explicitly indicate that descendant tables are included.

	`alias`

	A substitute name for the target table. When an alias is provided, it completely hides the actual name of the table. For example, given `DELETE FROM foo AS f`, the remainder of the `DELETE` statement must refer to this table as `f` not `foo`.

	`from_item`

	A table expression allowing columns from other tables to appear in the `WHERE` condition. This uses the same syntax as the `FROM` clause of a [SELECT](/docs/sql-stmts/select.md) statement; for example, you can specify an alias for the table name. Do not repeat the target table in the from_item, unless you wish to set up a self-join (in which case it must appear with an alias in the from_item).

	`condition`

	An expression that returns a value of type `boolean`. Apache Cloudberry deletes only those rows for which this expression returns `true`.

	`cursor_name`

	The name of the cursor to use in a `WHERE CURRENT OF` condition. The row to be deleted is the one most recently fetched from this cursor. The cursor must be a non-grouping query on the `DELETE`'s target table. Note that `WHERE CURRENT OF` cannot be specified together with a Boolean condition. See [DECLARE](/docs/sql-stmts/declare.md) for more information about using cursors with `WHERE CURRENT OF`.

	The `DELETE...WHERE CURRENT OF` cursor statement can only be run on the server, for example in an interactive psql session or a script. Language extensions such as PL/pgSQL do not have support for updatable cursors.

	`output_expression`

	An expression to be computed and returned by the `DELETE` command after each row is deleted. The expression can use any column names of the table named by table_name or table(s) listed in `USING`. Write `*` to return all columns.

	`output_name`

	A name to use for a returned column.

	## Outputs

	On successful completion, a `DELETE` command returns a command tag of the form

	```sql
	DELETE <count>
	```

	The count is the number of rows deleted. If count is 0, no rows were deleted by the query (this is not considered an error).

	If the `DELETE` command contains a `RETURNING` clause, the result will be similar to that of a `SELECT` statement containing the columns and values defined in the `RETURNING` list, computed over the row(s) deleted by the command.

	## Notes

	The `RETURNING` clause is not supported when deleting from append-optimized tables.

	The `WHERE CURRENT OF` clause is not supported with replicated tables.

	Apache Cloudberry lets you reference columns of other tables in the `WHERE` condition by specifying the other tables in the `USING` clause. For example, to delete all films produced by a given producer, one can run:

	```sql
	DELETE FROM films USING producers
	WHERE producer_id = producers.id AND producers.name = 'foo';
	```
	What is essentially happening here is a join between `films` and `producers`, with all successfully joined `films` rows being marked for deletion. This syntax is not standard. A more standard way to accomplish this is:

	```sql
	DELETE FROM films
	WHERE producer_id IN (SELECT id FROM producers WHERE name = 'foo');
	```

	For a partitioned table, all of the child tables are locked during the `DELETE` operation when the Global Deadlock Detector is not enabled (the default). Only some of the leaf child tables are locked when the Global Deadlock Detector is enabled.

	## Examples

	Delete all films but musicals:

	```sql
	DELETE FROM films WHERE kind <> 'Musical';
	```

	Clear the table films:

	```sql
	DELETE FROM films;
	```

	Delete completed tasks, returning full details of the deleted rows:

	```sql
	DELETE FROM tasks WHERE status = 'DONE' RETURNING *;
	```

	Delete the row of tasks on which the cursor `c_tasks` is currently positioned:

	```sql
	DELETE FROM tasks WHERE CURRENT OF c_tasks;
	```

	Delete using a join:

	```sql
	DELETE FROM rank USING names WHERE names.id = rank.id AND
	name = 'Hannah';
	```

	## Compatibility

	This command conforms to the SQL standard, except that the `USING` and `RETURNING` clauses are Apache Cloudberry extensions, as is the ability to use `WITH` with `DELETE`.

	## See also

	[TRUNCATE](/docs/sql-stmts/truncate.md)