Unlock SQLite’s 2018 SQL Enhancements: Booleans, Window Functions, Upserts & More

SQLite is often underestimated but is a reliable, file‑based database capable of handling terabytes of data; it lacks a network layer but excels in many scenarios.

From version 3.22.0 to 3.26.0 (2018) SQLite added several SQL features:

Boolean literals and truth testing

Window functions

FILTER clause

INSERT … ON CONFLICT (upsert)

Rename column

Boolean literals and testing

SQLite treats BOOLEAN as an alias for INTEGER; true maps to 1 and false to 0. Since version 3.23.0 the keywords true and false are recognized and can be used in IS [NOT] TRUE/FALSE predicates. The UNKNOWN value is not supported; use NULL instead.

Example comparisons: WHERE c <> FALSE and WHERE c IS NOT FALSE When c is NULL, c <> FALSE yields UNKNOWN and filters the row, while c IS NOT FALSE evaluates to true, keeping the row. An alternative is:

WHERE c <> FALSE OR c IS NULL

Window functions

SQLite 3.25.0 introduced window functions with OVER clause support comparable to other databases. The only frame limitation is that RANGE can use only CURRENT ROW or UNBOUNDED PRECEDING/FOLLOWING; numeric offsets are not allowed.

FILTER clause

The FILTER clause is syntactic sugar that can be applied to aggregate functions within OVER queries, making the intent clearer. Example:

SELECT SUM(revenue) FILTER (WHERE product = 1) AS prod1_revenue

SQLite supports FILTER with windowed aggregates starting from version 3.25.0, but it does not work with aggregates in a plain GROUP BY query, where a CASE expression must be used instead.

INSERT … ON CONFLICT (Upsert)

Since version 3.24.0 SQLite supports the upsert syntax INSERT … ON CONFLICT, allowing either to ignore conflicts or to update the existing row. The syntax mirrors PostgreSQL, but the parser can confuse the ON keyword when it follows a SELECT. Adding a dummy WHERE TRUE clause resolves the ambiguity.

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

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

Rename column

SQLite added a non‑standard ALTER TABLE … RENAME COLUMN … TO command, enabling column renaming directly.

Other updates

In addition to SQL syntax changes, SQLite introduced several API updates in 2018. Detailed release notes are available on the official SQLite news page.