What New SQL Features Did SQLite Add in 2018?

SQLite’s New SQL Features in 2018

SQLite is often underestimated as a "toy" database, but it is a reliable, file‑based engine capable of handling terabytes of data. Starting with version 3.22.0 up to 3.26.0, SQLite added several important SQL features.

Feature Overview

Boolean literals and predicates

Window functions

FILTER clause

INSERT … ON CONFLICT (UPSERT)

Rename column

Other API updates

Boolean Literals and Predicates

SQLite treats TRUE and FALSE as integer values 1 and 0. Since version 3.23.0 the keywords TRUE and FALSE are recognized, and the predicates IS [NOT] TRUE and IS [NOT] FALSE are supported. The keyword UNKNOWN is not supported; NULL should be used instead.

Example comparisons: WHERE c <> FALSE and WHERE c IS NOT FALSE If c is NULL, the first expression evaluates to UNKNOWN (filtered out), while the second evaluates to TRUE and includes the row.

To emulate the same effect without the predicate, you can write:

WHERE c <> FALSE
   OR c IS NULL

Window Functions

SQLite 3.25.0 introduced window functions, bringing the OVER clause support close to that of PostgreSQL and SQL Server. The RANGE frame is limited to CURRENT ROW and UNBOUNDED PRECEDING/FOLLOWING; numeric or interval ranges are not supported.

FILTER Clause

The FILTER clause is syntactic sugar that allows a predicate to be attached directly to an aggregate function. Example:

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

SQLite supports FILTER on aggregate functions that use the OVER clause (since 3.25.0) but not on aggregates with a plain GROUP BY. Therefore, the classic CASE expression is still required for many use‑cases.

INSERT … ON CONFLICT (UPSERT)

Since version 3.24.0 SQLite added UPSERT support, allowing an INSERT to handle primary‑key or unique‑constraint conflicts by either ignoring the conflict or updating the existing row.

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

Because the parser cannot always distinguish the ON keyword, a harmless WHERE TRUE clause may be added before the ON CONFLICT clause.

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

Rename Column

SQLite also introduced the ability to rename a column using the standard syntax:

ALTER TABLE … RENAME COLUMN … TO …

Other Updates

Additional API changes and minor improvements were made in 2018; details can be found on the official SQLite news page.