SQLite’s 2018 SQL Upgrades: Booleans, Window Functions, FILTER, Upserts & Column Renaming

SQLite 3.22.0–3.26.0 (released 2018) added a set of modern SQL features that bring its dialect close to PostgreSQL and SQL Server while retaining its file‑based architecture.

Boolean Literals and Predicates

SQLite stores the BOOLEAN type as an integer (1 = true, 0 = false). Starting with 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 implemented; NULL must be used instead.

Comparison of three‑valued logic shows the practical impact: WHERE c <> FALSE filters out rows where c is NULL because the expression evaluates to UNKNOWN. By contrast: WHERE c IS NOT FALSE returns TRUE for NULL values, so those rows are retained. The equivalent explicit null‑check is: WHERE c <> FALSE OR c IS NULL Thus IS NOT FALSE provides a concise replacement for the longer OR c IS NULL pattern.

Window Functions

Version 3.25.0 introduced window functions with an OVER clause that mirrors PostgreSQL and SQL Server syntax. Supported frame specifications are limited to CURRENT ROW and UNBOUNDED PRECEDING/FOLLOWING; numeric or interval offsets (e.g., RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING) are not available. This limitation matched the state of SQL Server at the time and was later removed in PostgreSQL 11.

Additional gaps in SQLite 3.25.0:

No negative offsets in RANGE frames.

Functions such as LEAD and FIRST_VALUE lack RESPECT NULLS / IGNORE NULLS options.

Some window specifications cannot include an ORDER BY clause.

Despite these omissions, SQLite’s window‑function implementation is among the most feature‑rich in open‑source databases.

FILTER Clause

The FILTER clause attaches a predicate directly to an aggregate function, improving readability for conditional aggregation. Example without FILTER:

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

Using FILTER:

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

SQLite supports FILTER only for aggregates that already use an OVER clause (i.e., windowed aggregates). Plain GROUP BY aggregates still require the CASE expression.

INSERT … ON CONFLICT (Upsert)

Version 3.24.0 added the ON CONFLICT clause, enabling “upsert” semantics that either ignore a primary‑key/unique‑key violation or update the existing row. The syntax follows PostgreSQL:

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

A parsing ambiguity occurs when the ON keyword follows a SELECT without an intervening clause, because the parser cannot decide whether ON belongs to a join condition or to the upsert clause. Inserting a harmless WHERE TRUE resolves the ambiguity:

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

Rename Column

SQLite introduced a non‑standard ALTER TABLE … RENAME COLUMN … TO … statement, allowing column renaming without recreating the table. Standard SQL lacks this operation, so SQLite fills a practical gap for developers who need in‑place schema evolution.

Other 2018 Changes

Beyond SQL syntax, SQLite released several API updates in 2018. Detailed release notes are available on the official SQLite news page: https://www.sqlite.org/news.html.