What New SQL Features Did SQLite Add Between Versions 3.22 and 3.26?

1. Boolean Literals and IS TRUE/FALSE

SQLite treats the BOOLEAN type as an alias for INTEGER, representing true as 1 and false as 0. Starting with version 3.23.0, the keywords true and false are recognized and can be used directly in IS [NOT] TRUE|FALSE predicates. The UNKNOWN keyword 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 and filters the row out, while the second expression evaluates to TRUE, preserving the row. An equivalent explicit handling can be written as:

WHERE c <> FALSE
   OR c IS NULL

SQLite now supports boolean literals and IS [NOT] TRUE|FALSE checks, aligning it with most open‑source databases.

It still lacks IS [NOT] UNKNOWN; use IS [NOT] NULL instead.

2. Window Functions

SQLite 3.25.0 added support for window functions, bringing a modern SQL feature to the engine. The OVER clause works similarly to other databases, but the RANGE frame cannot use numeric or interval values—only CURRENT ROW and UNBOUNDED PRECEDING/FOLLOWING are allowed, matching the limitation present in SQL Server and PostgreSQL at the time of release.

No support for RANGE with datetime values.

Only UNBOUNDED and CURRENT ROW keywords are accepted.

Features such as DISTINCT within aggregates, WIDTH_BUCKET, RESPECT|IGNORE NULLS, and FROM FIRST|LAST are not supported.

3. FILTER Clause

The FILTER clause, introduced in SQLite 3.25.0, can be used with aggregate functions that have an OVER clause, allowing rows to be filtered before aggregation. It is not yet supported for aggregates used with GROUP BY, so developers must still rely on CASE expressions for those scenarios.

Example comparison:

SELECT SUM(revenue) total_revenue,
       SUM(CASE WHEN product = 1 THEN revenue END) prod1_revenue
FROM sales;

versus

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

4. INSERT … ON CONFLICT (Upsert)

SQLite 3.24.0 introduced the upsert syntax, allowing an INSERT statement to handle primary‑key or unique‑constraint conflicts either by ignoring the conflict or by updating the existing row.

SQLite follows PostgreSQL’s syntax:

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

Because the parser cannot distinguish the ON keyword in a SELECT … FROM clause from the upsert clause, a harmless WHERE true can be added to disambiguate:

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

Upsert is a SQLite‑specific extension (not part of standard SQL).

If ON CONFLICT follows a SELECT … FROM, insert a dummy WHERE true clause.

5. Rename Column

SQLite also supports renaming a column in an existing table, a feature not defined in the SQL standard. The syntax mirrors that of other databases:

ALTER TABLE … RENAME COLUMN … TO …

Footnotes

SQLite often follows PostgreSQL syntax; Richard Hipp refers to this as “What Would PostgreSQL Do”.

A “baseline” table is one created with a CREATE TABLE statement; derived tables (e.g., sub‑queries) can have their column names altered via SELECT, FROM, or WITH clauses.

Similar functionality can sometimes be emulated with updatable views or derived columns.