Home  /  Reference

DELETE

DELETE removes values stored in user-created tables.

Syntax

DELETE FROM table_name AS alias USING from_item , WHERE condition
Field Use
DELETE FROM table_name The table whose values you want to remove.
alias Only permit references to table_name as alias.
USING from_item 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 Only remove rows which evaluate to true for condition.

Details

Known limitations

  • DELETE cannot be used inside transactions.
  • DELETE can reference user-created tables but not sources.
  • 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 ↑