Home  /  SQL commands

DELETE

DELETE removes values stored in user-created tables.

Syntax

DELETE FROM <table_name> [AS <alias>]
[USING <from_item> [, ...]]
[WHERE <condition>]
;
Syntax element Description
<table_name> The table whose values you want to remove.
AS <alias> Optional. The alias for the table. If specified, only permit references to <table_name> as <alias>.
USING from_item Optional. Table expressions whose columns you want to reference in the WHERE clause. This supports the same syntax as the FROM clause in SELECT statements, e.g. supporting aliases.
WHERE <condition> Optional. Only remove rows which evaluate to true for condition.

Details

Known limitations

  • DELETE cannot be used inside transactions.
  • DELETE can reference read-write tables but not sources or read-only tables.
  • Low performance. While processing a DELETE statement, Materialize cannot process other INSERT, UPDATE, or DELETE statements.

Examples

CREATE TABLE delete_me (a int, b text);
INSERT INTO delete_me
    VALUES
    (1, 'hello'),
    (2, 'goodbye'),
    (3, 'ok');
DELETE FROM delete_me WHERE b = 'hello';
SELECT * FROM delete_me ORDER BY a;

 a |    b
---+---------
 2 | goodbye
 3 | ok

CREATE TABLE delete_using (b text);
INSERT INTO delete_using VALUES ('goodbye'), ('ciao');
DELETE FROM delete_me
    USING delete_using
    WHERE delete_me.b = delete_using.b;
SELECT * FROM delete_me;

 a | b
---+----
 3 | ok

DELETE FROM delete_me;
SELECT * FROM delete_me;

 a | b
---+---

Privileges

The privileges required to execute this statement are:

  • USAGE privileges on the schemas that all relations and types in the query are contained in.
  • DELETE privileges on table_name.
  • SELECT privileges on all relations in the query.
    • NOTE: if any item is a view, then the view owner must also have the necessary privileges to execute the view definition. Even if the view owner is a superuser, they still must explicitly be granted the necessary privileges.
  • USAGE privileges on all types used in the query.
  • USAGE privileges on the active cluster.
Back to top ↑