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

SQLite is often underestimated, yet it is a highly reliable, file‑based database capable of handling terabytes of data, though it lacks a network layer. In 2018, SQLite introduced several significant SQL features across versions 3.22.0 to 3.26.0.

New Features in 2018

Boolean literals and checks

Window functions

FILTER clause

INSERT … ON CONFLICT (upsert)

Rename column syntax

1. Boolean Literals and Checks

SQLite treats TRUE and FALSE as integers 1 and 0, respectively. Starting with version 3.23.0, the keywords TRUE and FALSE are recognized, and the expressions IS [NOT] TRUE and IS [NOT] FALSE are supported. The keyword UNKNOWN is not supported; NULL should be used instead. WHERE c IS NOT FALSE When c is NULL, c IS NOT FALSE evaluates to TRUE, allowing rows with null values to pass the filter.

WHERE c <> FALSE
   OR c IS NULL

2. Window Functions

SQLite added support for the OVER clause and most window function capabilities, matching other major databases. The only limitation is that the RANGE frame cannot use numeric or interval values—only CURRENT ROW and UNBOUNDED PRECEDING/FOLLOWING are allowed.

3. FILTER Clause

The FILTER clause acts as syntactic sugar for conditional aggregation. It allows a predicate to be applied directly to an aggregate function, making queries easier to read.

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

In SQLite 3.25.0, FILTER is supported for aggregates that use the OVER clause, but not for aggregates combined with GROUP BY. Therefore, the traditional CASE expression must still be used in those cases.

4. INSERT … ON CONFLICT (Upsert)

SQLite introduced an upsert syntax that lets an INSERT automatically 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;

Because the parser cannot always distinguish the ON keyword in a SELECT statement from the upsert clause, a harmless WHERE TRUE can be added to disambiguate.

5. Rename Column

SQLite added a non‑standard but useful syntax to rename a column in an existing table:

ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

This feature is not part of the SQL standard but aligns with the syntax used by many other database systems.

Other 2018 Changes

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

Overall, SQLite’s 2018 enhancements brought its SQL capabilities much closer to those of MySQL, PostgreSQL, and SQL Server, while still retaining its lightweight, file‑based nature.