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

SQLite is often underestimated, but it is a reliable, file‑based database capable of handling terabyte‑scale data without a network layer. The article examines the SQL features added to SQLite during 2018 (versions 3.22.0 – 3.26.0).

New SQL Features in 2018

Boolean literals and predicates

Window functions

FILTER clause

INSERT … ON CONFLICT ("Upsert")

Rename column

Other minor updates

Boolean Literals and Predicates

Since version 3.23.0 SQLite treats the keywords true and false as integers 1 and 0. It also supports IS [NOT] TRUE|FALSE predicates, while the UNKNOWN keyword is no longer available; NULL should be used instead.

Example comparisons: WHERE c <> FALSE versus WHERE c IS NOT FALSE The first expression excludes rows where c is NULL, whereas the second includes them because NULL IS NOT FALSE evaluates to true.

Window Functions

SQLite 3.25.0 introduced window functions, aligning its OVER clause support with most other databases. The only notable limitation is that the RANGE frame cannot use numeric or interval offsets—only CURRENT ROW and UNBOUNDED PRECEDING/FOLLOWING are allowed.

FILTER Clause

The FILTER clause acts as a suffix to aggregate functions, allowing rows to be excluded before aggregation. SQLite supports FILTER with OVER aggregates starting from 3.25.0, but not with GROUP BY aggregates, so a CASE expression is still required for those cases.

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

can be written as

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

INSERT … ON CONFLICT (Upsert)

Starting with version 3.24.0 SQLite added the upsert syntax, enabling graceful handling of primary‑key or unique‑constraint conflicts. The syntax mirrors PostgreSQL’s ON CONFLICT clause.

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

Because the parser may confuse the ON keyword, a harmless WHERE TRUE clause can be inserted before the conflict clause.

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

Rename Column

SQLite also introduced a non‑standard ALTER TABLE … RENAME COLUMN … TO … statement, allowing column names to be changed directly.

ALTER TABLE my_table RENAME COLUMN old_name TO new_name;

Other 2018 Updates

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