New SQLite SQL Features (2018) – Boolean Literals, Window Functions, FILTER, UPSERT, and Column Renaming

SQLite is often underestimated but is a reliable, file‑based database capable of handling terabyte‑scale data without a network layer; this article examines the most recent SQL features added to SQLite over the past year.

Unlike a traditional server, SQLite is a library that can be embedded in many applications, making it the most widely deployed database engine with no licensing restrictions, and it now supports advanced SQL constructs.

The new SQL capabilities introduced from version 3.22.0 to 3.26.0 include:

Boolean literals and predicates

Window functions

FILTER clause

INSERT … ON CONFLICT (UPSERT)

Rename column syntax

Boolean literals and predicates

SQLite treats the BOOLEAN type as an integer, mapping true to 1 and false to 0. Since version 3.23.0 it supports IS [NOT] TRUE|FALSE predicates, while the UNKNOWN keyword is not supported; NULL can be used instead.

Example comparisons:

<code>WHERE c <> FALSE</code>

and

<code>WHERE c IS NOT FALSE</code>

When c is NULL , the first expression evaluates to UNKNOWN (and is filtered out), whereas the second returns TRUE , keeping the row.

To emulate the first behavior you can add an explicit OR c IS NULL clause:

<code>WHERE c <> FALSE OR c IS NULL</code>

Window functions

Added in SQLite 3.25.0, window functions use the OVER clause with syntax similar to PostgreSQL and SQL Server. The only limitation is that the RANGE frame can only specify UNBOUNDED PRECEDING , UNBOUNDED FOLLOWING , or CURRENT ROW ; numeric or interval ranges are not supported.

FILTER clause

The FILTER clause is syntactic sugar for conditional aggregation. It can be used with aggregate functions in an OVER clause but not with aggregates that have a GROUP BY clause in SQLite.

Example without FILTER :

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

Example with FILTER (supported only in windowed aggregates):

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

INSERT … ON CONFLICT (UPSERT)

Since version 3.24.0 SQLite supports the UPSERT syntax, allowing graceful handling of primary‑key or unique‑constraint violations. The syntax mirrors PostgreSQL:

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

Because the parser cannot always distinguish the ON keyword, a WHERE TRUE clause may be inserted to disambiguate:

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

Rename column

SQLite also adds a non‑standard ALTER TABLE … RENAME COLUMN … TO … statement to rename columns directly.

Other notes

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