DELETE

Delete rows from the table.

Summary

[ WITH [ RECURSIVE ] with_query [, ...] ]
DELETE FROM [ONLY] table [[AS] alias]
      [USING usinglist]
      [WHERE condition | WHERE CURRENT OF cursor_name]
      [RETURNING * | output_expression [[AS] output_name] [, …]]

describe

DELETE Deletes the row that satisfies the WHERE clause from the specified table. If the WHERE clause does not exist, the result is that all rows in the table are deleted. The result is a valid but empty table.

By default, DELETE deletes rows from the specified table and all its subtables. If you only want to delete from the specific table mentioned, you must use the ONLY clause.

There are two ways to delete rows in a table using the information contained in other tables in the database: use subselect, or specify another table in the USING clause. Which technology is more suitable depends on the specific situation.

If the WHERE CURRENT OF clause is specified, the deleted row is the latest row obtained from the specified cursor.

The WHERE CURRENT OF clause is not supported in the copy table.

The optional RETURNING clause causes DELETE to calculate and return a value based on each row that is actually deleted. Any expression that uses the columns of the table and/or columns of other tables mentioned in USING can be calculated. The syntax of the RETURNING list is the same as the syntax of the output list of SELECT.

You must have DELETE privileges on the table to be deleted from it.

Note: By default, the YMatrix database acquires the EXCLUSIVE lock on the table for the DELETE operation on the heap table. When the global deadlock detector is enabled, the locking mode of the DELETE operation on the heap table is ROW EXCLUSIVE.

Output

After successful completion, the DELETE command returns the following command label:

DELETE count

count is the number of rows that have been deleted. If count is 0, the query does not delete any rows (this is not considered an error).

If the DELETE command contains the RETURNING clause, the result will be similar to the result of the SELECT statement, which contains the columns and values defined in the RETURNING list, which are calculated on the rows deleted by the command.

Parameters

with_query

The WITH clause allows you to specify one or more subqueries that can be referenced by name in a DELETE query.
For DELETE commands containing WITH clauses, the clause can only contain SELECT statements, while the WITH clause cannot contain data modification commands (INSERT, UPDATE, or DELETE).

ONLY

If specified, only rows are removed from the named table. If not specified, any tables inherited from the named table are also processed.

table

The name of the existing table (can be specified in schema).

alias

Alternative name for the target table. Once an alias is provided, it will completely hide the actual name of the table. For example, given DELETE FROM foo AS f, the rest of the DELETE statement must refer to this table as f instead of foo.

usinglist

A list of table expressions that allow columns in other tables to appear with WHERE conditions. This is similar to a list of tables that can be specified in the FROM clause of a SELECT statement. For example, you can specify an alias for a table name. Do not repeat the target table in usinglist unless you want to set up a self-connection.

condition

This expression returns a Boolean value that determines the row to be deleted.

cursor_name

Cursor name used in the WHERE CURRENT OF condition. The row to be deleted is the most recent row obtained from the cursor. The cursor must be a simple non-grouping query to the DELETE target table.
WHERE CURRENT OF cannot be specified with the Boolean condition.
DELETE...WHERE CURRENT OF Cursor statement can only be executed on the server, for example in an interactive psql session or script. Language extensions (such as PL/pgSQL) do not support updating cursors.

output_expression

The expression calculated and returned by the DELETE command after each row is deleted. This expression can use any column name of one or more tables listed in USING. Enter * to return all columns.

output_name

The name of the column used to return.

Note

The YMatrix database allows you to refer to columns of other tables under WHERE conditions by specifying them in the USING clause. For example, name it Hannah from the rank table, you can do this:

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

What actually happens here is the join between rank and names, and all successfully joined lines are marked as deleted. This syntax is not a standard syntax. However, this join style is often easier to write and execute than a more standard sub-select style, such as:

DELETE FROM rank WHERE id IN (SELECT id FROM names WHERE name 
= 'Hannah');

When using DELETE to delete all rows of a table (for example: DELETE * FROM table;), the YMatrix database adds an implicit TRUNCATE command (when user permissions allow). The added TRUNCATE command frees disk space occupied by deleted rows without VACUUM on the table. This improves the scanning performance of subsequent queries and benefits ELT workloads that are often inserted and deleted in temporary tables.

Direct execution of UPDATE and DELETE commands on specific partitions (subtables) of partitioned tables is not supported. Instead, these commands must be executed on the root partition table (the table created using the CREATE TABLE command).

Example

Remove all films except musicals:

DELETE FROM films WHERE kind <> 'Musical';

Clear table films:

DELETE FROM films;

Delete completed tasks and return the full details of the deleted rows:

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

Use join to delete:

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

compatibility

This command is SQL-compliant, with the difference being that the USING and RETURNING clauses are YMatrix database extensions and the ability to use WITH and DELETE together.