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

SQLite is often underestimated but is a reliable, serverless database capable of handling terabytes of data. This article reviews the new SQL features added to SQLite between versions 3.22.0 and 3.26.0 in 2018.

Boolean literals and predicates

SQLite treats the Boolean type as an integer, mapping true to 1 and false to 0. Since version 3.23.0 the keywords true and false are recognized, and the predicates IS [NOT] TRUE|FALSE are supported, while UNKNOWN is not; NULL can be used instead.

Examples: WHERE c <> FALSE and WHERE c IS NOT FALSE When c is NULL, the first expression evaluates to UNKNOWN and filters the row, whereas the second expression returns true, keeping the row. An equivalent expression is WHERE c <> FALSE OR c IS NULL.

Window functions

Version 3.25.0 introduced window functions, bringing a major modern SQL capability to SQLite. The OVER clause is supported similarly to other databases, but the RANGE frame does not accept numeric or interval values—only CURRENT ROW and UNBOUNDED PRECEDING/FOLLOWING. Certain features such as IGNORE NULLS and negative offsets are not supported.

FILTER clause

The FILTER clause acts as a suffix to aggregate functions, allowing rows to be conditionally excluded before aggregation. SQLite supports FILTER with windowed aggregates from version 3.25.0, but not with GROUP BY aggregates, so CASE expressions remain necessary.

SELECT SUM(revenue) total_revenue,
       SUM(CASE WHEN product = 1 THEN revenue END) prod1_revenue
-- versus
SELECT SUM(revenue) total_revenue,
       SUM(revenue) FILTER (WHERE product = 1) prod1_revenue

INSERT … ON CONFLICT (Upsert)

Since version 3.24.0 SQLite supports the UPSERT syntax, allowing INSERT statements 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;

When the parser cannot distinguish the ON keyword, adding a harmless WHERE TRUE clause resolves the ambiguity.

Rename column

SQLite added the ability to rename a column in an existing table using the standard syntax:

ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

Other updates

In 2018 SQLite also introduced several API changes; details can be found on the official SQLite news page.