Skip to main content
SereneDBSereneDB
961Contact Us

DELETE

The DELETE statement removes rows from the table identified by the table-name. If the WHERE clause is not present, all records in the table are deleted. If a WHERE clause is supplied, then only those rows for which the WHERE clause results in true are deleted. Rows for which the expression is false or NULL are retained.

Examples​

Remove the rows matching the condition i = 2 from the database:

SQL
DELETE FROM tbl WHERE i = 2;

Delete all rows in the table tbl:

SQL
DELETE FROM tbl;

USING Clause​

The USING clause allows deleting based on the content of other tables or subqueries. A row that several rows of the USING clause match is deleted, counted and returned by RETURNING once.

RETURNING Clause​

The RETURNING clause allows returning the deleted values. It uses the same syntax as the SELECT clause except the DISTINCT modifier is not supported.

SQL
CREATE TABLE employees (name VARCHAR, age INTEGER);
INSERT INTO employees VALUES ('Kat', 32);
DELETE FROM employees RETURNING name, 2025 - age AS approx_birthyear;
 name | approx_birthyear
------+------------------
 Kat  |             1993

Retention​

A job that deletes rows older than a cutoff keeps a table to a retention period:

SQL
CREATE JOB events_retention EVERY 1 HOUR AS    DELETE FROM events WHERE at < now() - INTERVAL '30 days';
EXECUTE JOB events_retention;
VACUUM (REFRESH_TABLE) events;
SELECT body FROM events ORDER BY body;
DROP JOB events_retention;
 body
--------
 recent

# DOCS_TEST_END

On a search table, the log space of a delete grows with the number of stretches of consecutive rows it removes, not with the number of rows: the oldest rows of a table filled in time order are one stretch, however many rows go. Rows deleted one by one from among rows that stay are a stretch each. Finding the rows to delete still reads each of them.

Syntax​

Syntax

The TRUNCATE Statement​

The TRUNCATE statement removes all rows from a table, acting as an alias for DELETE FROM without a WHERE clause:

SQL
TRUNCATE tbl;

TRUNCATE can name several tables; they are emptied in one transaction, so either all of them are emptied or, if any of them fails, none is. As in PostgreSQL, a table that another table references with a foreign key can only be truncated together with the referencing table. By default (RESTRICT) the statement is refused otherwise, even when the referencing table holds no rows. CASCADE also truncates every table that references a truncated table, and the tables that reference those:

SQL
TRUNCATE orders;
TRUNCATE orders CASCADE;
SELECT (SELECT count(*) FROM orders) AS orders, (SELECT count(*) FROM order_lines) AS lines;
db error: ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "order_lines" references "orders".
HINT: Truncate table "order_lines" at the same time, or use TRUNCATE ... CASCADE.

 orders | lines
--------+-------
      0 |     0

RESTART IDENTITY also restarts the sequences the truncated tables own, those behind their SERIAL columns and those attached with OWNED BY, so new rows are numbered from the start again. CONTINUE IDENTITY, the default, leaves them where they are. The restart belongs to the transaction: if it rolls back, the sequences continue where they were:

SQL
TRUNCATE tickets RESTART IDENTITY;
INSERT INTO tickets (title) VALUES ('third');
SELECT id, title FROM tickets;
 id | title
----+-------
  1 | third

PostgreSQL makes TRUNCATE wait for the transactions that are writing to the table. SereneDB waits only for a commit that is writing the table at that moment: TRUNCATE fails with a serialization error (40001) when another transaction has added rows to the table that the truncating transaction does not see, because they are not committed yet or were committed after its snapshot, and the transaction can be retried. On a search table, any open write to the table refuses TRUNCATE the same way, and while a transaction that truncated a search table is open, writes to that table from other transactions fail with 40001.

Limitations on Reclaiming Memory and Disk Space​

Running DELETE does not mean space is reclaimed. In general, rows are only marked as deleted. SereneDB reclaims space when performing a CHECKPOINT. VACUUM currently does not reclaim space.

Edit this page ↗