DELETE
View as MarkdownDELETE 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
DELETEcannot be used inside transactions.DELETEcan reference read-write tables but not sources or read-only tables.- Concurrency.
DELETEdoes not take a table-wide write lock.DELETE,UPDATE, andINSERT ... SELECTrun under optimistic concurrency control: a bounded number run concurrently, so a long-running one can delay other such statements (including on unrelated tables), and a statement that conflicts with a concurrent write to the same table is retried automatically. PlainINSERT ... VALUESis not blocked. - Large deletes. A single
DELETEthat matches a very large number of rows can exceedmax_result_sizeorstatement_timeout. See LargeDELETEstatements.
Large DELETE statements
Materialize processes a DELETE by reading every row the statement matches back
through the cluster so it can retract it. The work therefore scales with the
number of rows matched, not just the size of the table or the complexity of the
WHERE clause.
A DELETE has two limits:
- Size. The rows a
DELETEmatches can total at mostmax_result_size(default 1 GiB). A largerDELETEfails withtotal result exceeds max sizeand removes no rows. - Time.
statement_timeout(default 60 seconds) covers the statement until its commit, including time spent waiting behind otherDELETE,UPDATE, andINSERT ... SELECTstatements. If the timeout fires during the commit, theDELETEcan still succeed.
To tell whether a DELETE is at risk before running it, time the matching
SELECT: a DELETE does roughly the same work as selecting the rows it matches.
If SELECT * FROM <table> WHERE <condition> does not return comfortably within
statement_timeout, a DELETE with the same WHERE clause will not complete
either. A DELETE that fails with a statement timeout removes no rows (the
statement is atomic), so it is safe to retry as smaller batches.
To delete more than fits in one statement, split it into smaller statements that each match fewer rows, for example by key range:
-- Instead of one large delete:
DELETE FROM events WHERE ts < '2026-01-01';
-- Delete in smaller batches by key range:
DELETE FROM events WHERE ts < '2026-01-01' AND id BETWEEN 0 AND 99999;
DELETE FROM events WHERE ts < '2026-01-01' AND id BETWEEN 100000 AND 199999;
-- ...continue until all matching rows are removed.
Raising statement_timeout or increasing the cluster size can help a moderately
large DELETE complete, but a very large read-back can still fail, so splitting
the statement is the reliable approach. The same guidance applies to large
UPDATE statements, which use the same mechanism.
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:
USAGEprivileges on the schemas that all relations and types in the query are contained in.DELETEprivileges ontable_name.SELECTprivileges 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.
USAGEprivileges on all types used in the query.USAGEprivileges on the active cluster.