SQLite Has No TRUNCATE Statement

Test setups often empty their tables with TRUNCATE TABLE between runs. PostgreSQL, MySQL and MariaDB have it, and SQLite refuses it.

SQLite empties a table quickly with a DELETE FROM t that has no WHERE clause. On MySQL and MariaDB, TRUNCATE also resets the AUTO_INCREMENT counter, which DELETE leaves alone.

What Each Engine Answered

On a table made with

CREATE TABLE t (x INTEGER);
INSERT INTO t VALUES (1);

the example

TRUNCATE TABLE t;
SELECT COUNT(*) FROM t;

gave these answers:

What to Write Instead

This form answers the same on every engine:

DELETE FROM t;
SELECT COUNT(*) FROM t;

Use DELETE FROM t.

What BareSQL Portable Reports

Saved as example.sql, the example gets this from baresql lint --all:

$ baresql lint --all example.sql
example.sql:3:1: error: SQLite has no TRUNCATE; PostgreSQL, MySQL and MariaDB have it. [truncate]
    TRUNCATE TABLE
    Use DELETE FROM t.

1 problem: 1 error.

baresql rules truncate prints the same answers in the terminal, and the BareSQL Portable page lists every difference BareSQL knows.