What New SQL Features Did SQLite Add in 2018? A Deep Dive

SQLite is frequently underestimated as a "toy" database, yet it can reliably handle terabyte‑scale data without a network layer. In 2018 the engine introduced several significant SQL enhancements.

Boolean literals and checks

Window functions

FILTER clause

INSERT … ON CONFLICT (upsert)

Rename column syntax

Additional API updates

01 Boolean literals and checks

SQLite treats the Boolean type name as an alias for INTEGER, using 1 for true and 0 for false. Since version 3.23.0 the keywords true and false are recognized, and IS [NOT] TRUE|FALSE predicates are supported. The UNKNOWN keyword is not supported; NULL should be used instead.

Example: WHERE c <> FALSE When c is NULL, c <> FALSE yields UNKNOWN and the row is filtered out, whereas c IS NOT FALSE evaluates to TRUE, keeping the row.

02 Window functions

SQLite now supports the OVER clause with syntax comparable to other major databases. The only limitation is that the RANGE frame cannot use numeric or interval distances; it only accepts CURRENT ROW and UNBOUNDED PRECEDING/FOLLOWING. This mirrors the behavior of SQLite 3.25.0, SQL Server, and PostgreSQL at the time.

03 FILTER clause

The FILTER clause acts as a suffix to aggregate functions, allowing rows to be conditionally excluded before aggregation. It simplifies expressions such as:

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

From version 3.25.0 SQLite supports FILTER with windowed aggregates, but not with aggregates that use GROUP BY. Consequently, developers must still rely on CASE expressions for grouped queries.

04 Upsert (INSERT … ON CONFLICT)

SQLite introduced the upsert syntax, allowing graceful handling of primary‑key or unique‑constraint conflicts. The syntax mirrors PostgreSQL:

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

A parsing ambiguity arises because the keyword ON can be interpreted as part of a SELECT join or as the start of an upsert clause. Adding a harmless WHERE TRUE clause before ON CONFLICT resolves the issue.

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

05 Rename column

SQLite added a non‑standard but widely used syntax to rename a column:

ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

06 Other news

Beyond SQL syntax, SQLite released several API changes in 2018. Detailed release notes are available on the official SQLite news page.

07 Footnotes

SQLite often follows PostgreSQL behavior, a principle Richard Hipp calls “What Would PostgreSQL Do”.