New SQLite SQL Features Introduced in 2018 (Versions 3.22.0–3.26.0)

SQLite is often underestimated, yet it is a highly reliable, file‑based SQL engine capable of handling terabyte‑scale data, though it lacks a network layer; its open‑source nature and lack of licensing restrictions make it a popular choice for many applications.

This article examines the SQL features added to SQLite between versions 3.22.0 and 3.26.0, focusing on six main areas: boolean literals and predicates, window functions, the FILTER clause, INSERT … ON CONFLICT (UPSERT), column renaming, and other API changes.

Boolean literals and predicates

Starting with version 3.23.0, SQLite treats the keywords true and false as integer 1 and 0, respectively, and supports 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

When c is NULL , the first expression yields UNKNOWN (filtered out), while the second evaluates to TRUE , thus retaining the row. An alternative handling of NULL values is:

WHERE c <> FALSE  
   OR c IS NULL

Window functions

Version 3.25.0 introduced window functions with OVER support comparable to other major databases. The only notable limitation is that the RANGE frame cannot specify numeric or interval distances—only CURRENT ROW and UNBOUNDED PRECEDING/FOLLOWING are allowed.

FILTER clause

The FILTER clause provides syntactic sugar for conditional aggregation. For example:

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

can be rewritten as:

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

SQLite supports FILTER with windowed aggregates (since 3.25.0) but not with plain GROUP BY aggregates, requiring the traditional CASE expression workaround.

INSERT … ON CONFLICT (UPSERT)

From version 3.24.0, SQLite implements the UPSERT syntax, allowing conflict handling during INSERTs. Example:

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

If the parser misinterprets ON as a join clause, adding a dummy WHERE TRUE clause resolves the ambiguity:

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

Rename column

SQLite also supports renaming a column in an existing table using the standard syntax:

ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

While this feature is not part of the SQL standard, SQLite follows the syntax used by many other database products.

Other 2018 updates

Beyond SQL syntax, SQLite introduced several API changes in 2018; details can be found on the official SQLite news page.

Footnotes

SQLite often mirrors PostgreSQL syntax, a design choice referred to by its creator as “What Would PostgreSQL Do”.

The term “baseline table” refers to tables created with a CREATE TABLE statement; derived tables from SELECT statements can have their column names altered via SELECT, FROM, or WITH clauses.

Some missing features (e.g., IS [NOT] UNKNOWN ) can be emulated using NULL handling or updatable views.

Original source: https://modern-sql.com/blog/2019-01/sqlite-in-2018 (author: Markus Winand).