DELETE

Description

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 an empty table.

Precautions

The table owner, users with the DELETE permission on the current table, and users with the DELETE ANY TABLE permission can delete data from the table. System administrators automatically have this permission when separation of duties is disabled. Additionally, you must have the SELECT permission on the tables referenced by the USING clause and the tables read by condition.
For a DELETE statement whose subquery is a STREAM plan, UPDATE cannot be performed on the deleted row data.

Syntax

DELETE [/*+ plan_hint */] [IGNORE] FROM {table_name [partition_clause] | view_name}
    [ WHERE condition ]
    [ ORDER BY { expression  [ ASC | DESC ] } [ NULLS { FIRST | LAST } ] ]
    [ LIMIT { count } ]
    [ RETURNING { * | { output_expr [ [ AS ] output_name ] } [, ...] } ];

Parameters

plan_hint
Follows the DELETE keyword in the /*+ */ format. It is used to optimize the plan of a DELETE statement block. In each statement, only the first /*+ plan_hint */ comment block takes effect as a hint. Multiple hints can be written.
IGNORE
When the DELETE statement uses the IGNORE keyword, some ERROR-level errors can be degraded to WARNING-level errors, and invalid values can be adjusted to the closest values based on error scenarios. GaussDB supports the following error downgrade scenarios:

sql_mode set to loose
table_name
Specifies the name (optionally schema-qualified) of the target table.

Value range: existing table names.
partition_clause
Deletes a specified partition.

PARTITION { ( partition_name | subpartition_name [ , ...] )

For keywords, see SELECT.
view_name
Specifies name of the target view.
The restrictions on deleting a view are as follows:
- The DELETE operation can be performed only on columns that directly reference user columns in the base table.
- A view must contain at least one updatable column. For details about updatable columns, see CREATE VIEW.
- Views that contain the DISTINCT, GROUP BY, HAVING, LIMIT, or OFFSET clause at the top layer are not supported.
- Views that contain set operations (UNION and EXCEPT) at the top layer are not supported.
- Views whose target lists contain aggregate functions, window functions, or return set functions (such as array_agg, json_agg, and generate_series) are not supported.
- Table types supported in views include ordinary tables, temporary tables, global temporary tables, partitioned tables, Ustore tables, and Astore tables.
- A join view can delete only the key-preserved tables in the view. If there is only one key-preserved table, the data in the table is deleted. If there are multiple key-preserved tables, only the data in the first key-preserved table following FROM is deleted. For details about the key-preserved table, see CREATE VIEW.
- The DELETE operation cannot be performed on the system view.
NULLS FIRST
Specifies that null values appear before non-null values in the sort ordering. This is the default when DESC is specified.
NULLS LAST
Specifies that null values appear after non-null values in the sort ordering. This is the default when DESC is not specified.
condition
Specifies an expression that returns a Boolean value. Only rows for which this expression returns true will be deleted. To prevent unexpected outcomes, avoid using numeric types such as int for condition, because these types can be implicitly converted into Boolean values: 0 becomes false, while other values become true.
ORDER BY
For keywords, see SELECT.
LIMIT
For keywords, see SELECT.
RETURNING
Returns the inserted rows. The syntax of the RETURNING list is identical to that of the output list of SELECT.

Example

Delete some data.

-- Create a table.
m_db=# CREATE TABLE test_t1(col1 INT,col2 INT);
m_db=# INSERT INTO test_t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (4, 6);

-- Delete all records from a table.
m_db=# DELETE FROM test_t1 WHERE col1 = 4;

-- Query.
m_db=# SELECT * FROM test_t1;
 col1 | col2 
------+------
    1 |    1
    2 |    2
    3 |    3
(3 rows)

Delete all data.

-- Delete all data.
m_db=# DELETE FROM test_t1;

-- Query.
m_db=# SELECT * FROM test_t1;
 col1 | col2 
------+------
(0 rows)

-- Drop the table.
m_db=# DROP TABLE test_t1;

Delete a view.

-- Create a schema.
m_db=# CREATE SCHEMA del_view;
CREATE SCHEMA
m_db=# SET CURRENT_SCHEMA = 'del_view';
SET

-- Create a table and insert data into the table.
m_db=# CREATE TABLE t1 (x1 int, y1 int);
CREATE TABLE
m_db=# CREATE TABLE t2 (x2 int PRIMARY KEY, y2 int);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t2_pkey" for table "t2"
CREATE TABLE
m_db=# CREATE TABLE tdata (x INT PRIMARY KEY, y INT);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "tdata_pkey" for table "tdata"
CREATE TABLE
m_db=# CREATE TABLE tinfo (z INT PRIMARY KEY, comm VARCHAR(20));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "tinfo_pkey" for table "tinfo"
CREATE TABLE
m_db=# INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (5, 5);
INSERT 0 4
m_db=# INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (5, 5);
INSERT 0 4
m_db=# INSERT INTO tdata VALUES (1, 1), (2, 2), (3, 3);
INSERT 0 3
m_db=# INSERT INTO tinfo VALUES (1,'one'), (2, 'two'), (3, 'three'), (5, 'wrong three');
INSERT 0 4

-- Create a single table view.
m_db=# CREATE VIEW v_del1 AS SELECT * FROM t1;
CREATE VIEW

-- Delete data from t1 using a view.
m_db=# DELETE FROM v_del1 where y1 = 3;
DELETE 1

-- Create a multi-table join view.
m_db=# CREATE VIEW vvt1t2 AS SELECT * FROM t1, t2 WHERE x1 = x2;
CREATE VIEW
m_db=# CREATE VIEW vv_dup AS SELECT td1.x x1, td1.y y1, td2.x x2, td2.y y2 FROM tdata td1, tdata td2, tinfo WHERE td2.y=tinfo.z AND td1.x=td2.y;
CREATE VIEW

-- Delete the multi-table join view.
m_db=# SELECT * FROM vvt1t2;
 x1 | y1 | x2 | y2
----+----+----+----
  1 |  1 |  1 |  1
  2 |  2 |  2 |  2
  5 |  5 |  5 |  5
(3 rows)

m_db=# DELETE FROM vvt1t2 WHERE y2 = 5;
DELETE 1
m_db=# SELECT * FROM vvt1t2;
 x1 | y1 | x2 | y2
----+----+----+----
  1 |  1 |  1 |  1
  2 |  2 |  2 |  2
(2 rows)

m_db=# SELECT * FROM vv_dup;
 x1 | y1 | x2 | y2
----+----+----+----
  1 |  1 |  1 |  1
  2 |  2 |  2 |  2
  3 |  3 |  3 |  3
(3 rows)

m_db=# DELETE FROM vv_dup WHERE y1 = 2;
DELETE 1
m_db=# SELECT * FROM vv_dup;
 x1 | y1 | x2 | y2
----+----+----+----
  1 |  1 |  1 |  1
  3 |  3 |  3 |  3
(2 rows)

-- Drop the schema.
m_db=# DROP SCHEMA del_view;
NOTICE:  drop cascades to 7 other objects
DETAIL:  drop cascades to table t1
drop cascades to table t2
drop cascades to table tdata
drop cascades to table tinfo
drop cascades to view v_del1
drop cascades to view vvt1t2
drop cascades to view vv_dup
DROP SCHEMA