Overview of SQLite’s New SQL Features in 2018 (Versions 3.22.0–3.26.0)

SQLite is often underestimated but is a reliable, file‑based database engine capable of handling terabytes of data, though it lacks a network layer.

In 2018 SQLite added several SQL features across versions 3.22.0 to 3.26.0, including boolean literals, window functions, FILTER clause, UPSERT (INSERT … ON CONFLICT), and column renaming.

Boolean literals and predicates

SQLite treats the BOOLEAN type as an alias for INTEGER, with true = 1 and false = 0. Since version 3.23.0 the keywords TRUE and FALSE are recognized, and the predicates IS [NOT] TRUE/FALSE are supported. Example comparisons:

WHERE c <> FALSE

WHERE c IS NOT FALSE

When c is NULL, the first expression yields UNKNOWN and is filtered out, while the second evaluates to TRUE, keeping the row.

Window functions

Version 3.25.0 introduced window functions with OVER clauses. The syntax is similar to other databases, but SQLite does not support RANGE with numeric offsets or datetime values; only CURRENT ROW and UNBOUNDED PRECEDING/FOLLOWING are allowed.

FILTER clause

From version 3.25.0, aggregate functions used with OVER can include a FILTER clause, e.g.:

SELECT SUM(revenue) total_revenue,
       SUM(revenue) FILTER (WHERE product = 1) prod1_revenue
FROM sales;

However, FILTER is not supported for aggregates without OVER.

INSERT … ON CONFLICT (UPSERT)

Since version 3.24.0 SQLite supports UPSERT syntax, allowing conflict handling during INSERT:

INSERT INTO target(col1, col2)
SELECT * FROM source
ON CONFLICT(id) DO UPDATE SET val = excluded.val;

When the parser cannot distinguish ON as part of SELECT, adding a dummy WHERE true clause resolves the ambiguity.

Rename column

SQLite now allows renaming a column with the standard syntax:

ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

Other 2018 changes include API updates and various limitations noted in the original article.