What New SQL Features Did SQLite Add in 2018?

Introduction

SQLite is often underestimated but can handle terabytes of data. Although it lacks a network layer, it is widely used for embedded storage. This article reviews the SQL features added to SQLite between versions 3.22.0 and 3.26.0 (released in 2018).

Boolean literals and predicates

From version 3.23.0 SQLite treats the keywords true and false as integers 1 and 0. The IS [NOT] TRUE|FALSE predicate is supported, while UNKNOWN is not; NULL can be used instead. Example comparisons illustrate the three‑valued logic and how IS NOT FALSE can replace an explicit OR … IS NULL clause.

Window functions

SQLite 3.25.0 introduced window functions with OVER clauses, matching most other databases. The only limitation is that the RANGE frame cannot use numeric offsets—only CURRENT ROW or UNBOUNDED PRECEDING/FOLLOWING. Other features such as DISTINCT within aggregates, WIDTH_BUCKET, and RESPECT|IGNORE NULLS are still missing.

FILTER clause

The FILTER (WHERE …) clause, syntactic sugar for conditional aggregation, is supported for aggregate functions that use OVER, but not for plain GROUP BY aggregates. The article shows a before‑and‑after example comparing a CASE expression with the FILTER syntax.

INSERT … ON CONFLICT (Upsert)

Starting with SQLite 3.24.0, the UPSERT syntax allows handling primary‑key or unique‑constraint conflicts directly in an INSERT statement. The syntax mirrors PostgreSQL’s ON CONFLICT clause. A parser ambiguity requires adding a dummy WHERE TRUE clause when the ON CONFLICT follows a SELECT … FROM sub‑query.

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

Rename column

SQLite added a non‑standard ALTER TABLE … RENAME COLUMN … TO … command, allowing column renaming without recreating the table. This feature is not part of the SQL standard.

Other changes

In 2018 SQLite also introduced several API changes; details are available on the official SQLite news page.