New SQLite SQL Features in 2018: Boolean Literals, Window Functions, FILTER Clause, Upsert, and Column Renaming

SQLite is often underestimated, but it is a reliable, file‑based database capable of handling terabytes of data without a network layer. In 2018, SQLite added several important SQL features across versions 3.22.0 to 3.26.0.

New features include:

Boolean literals and predicates

Window functions

FILTER clause

INSERT … ON CONFLICT ("Upsert")

Column renaming

Additional notes on Modern‑SQL.com

Boolean literals and predicates

SQLite treats the type name BOOLEAN as an integer, with true and false represented by 1 and 0. Since version 3.23.0, the keywords true and false are mapped to these numeric values, and the predicates IS [NOT] TRUE|FALSE are supported. The keyword UNKNOWN is not supported; NULL can be used instead.

Example comparisons:

WHERE c <> FALSE

and

WHERE c IS NOT FALSE

If c is NULL , the first expression evaluates to UNKNOWN (and is filtered out), while the second evaluates to TRUE , keeping the row. An equivalent explicit handling can be written as:

WHERE c <> FALSE
   OR c IS NULL

SQLite now supports Boolean literals similarly to other open‑source databases, except it does not support IS [NOT] UNKNOWN (use NULL instead).

Window functions

Version 3.25.0 introduced window functions with OVER support comparable to other databases. The only notable limitation is that the RANGE frame cannot use numeric or interval offsets—only CURRENT ROW and UNBOUNDED PRECEDING/FOLLOWING . This limitation is shared with SQL Server and PostgreSQL at the time of release.

FILTER clause

The FILTER clause, syntactic sugar for conditional aggregation, is supported in SQLite 3.25.0 for aggregate functions that use an OVER clause, but not for aggregates in a plain GROUP BY context. Example:

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

can be rewritten as:

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

Since SQLite does not yet support FILTER with GROUP BY , developers must continue using CASE expressions.

INSERT … ON CONFLICT ("Upsert")

Starting with version 3.24.0, SQLite added UPSERT capability, allowing INSERT statements to handle primary‑key or unique‑constraint conflicts by either ignoring them or updating the existing row. The syntax follows PostgreSQL:

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

Because the parser cannot always distinguish the ON keyword as part of a SELECT join or an UPSERT clause, a dummy WHERE TRUE can be added to disambiguate:

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

Column renaming

SQLite also supports renaming a column with the standard syntax:

ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

This feature is not part of the SQL standard but aligns SQLite with other major databases.

Other notes

In addition to SQL syntax changes, SQLite introduced several API updates in 2018; details can be found on the official SQLite news page.

Footnotes

SQLite often mirrors PostgreSQL syntax, a design choice referred to by its creator as "What Would PostgreSQL Do" (WWPD).

Derived tables (e.g., sub‑queries) can have column names altered via SELECT , FROM , or WITH clauses.

Some features can be simulated using updatable views or derived columns.

Original source: https://modern-sql.com/blog/2019-01/sqlite-in-2018 (author: Markus Winand).