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

SQLite is often underestimated but is a reliable, file‑based database capable of handling terabytes of data, though it lacks a network layer.

From version 3.22.0 to 3.26.0 (2018) SQLite added several SQL features:

Boolean literals and predicates

Window functions

FILTER clause

INSERT … ON CONFLICT (upsert)

Rename column

Other updates

Boolean literals and predicates

SQLite treats the BOOLEAN type as an integer, with true = 1 and false = 0. Since version 3.23.0 the keywords true and false are recognized, and the expressions IS [NOT] TRUE|FALSE are supported. The keyword UNKNOWN is not supported; use NULL instead.

Using true/false literals improves readability in INSERT and UPDATE statements.

Example of handling NULL values:

WHERE c <> FALSE
   OR c IS NULL

Alternatively, c IS NOT FALSE can be used to include rows where c is NULL.

Window functions

SQLite supports the OVER clause similar to other databases. The only limitation is that RANGE frames cannot specify numeric or interval distances; only CURRENT ROW and UNBOUNDED PRECEDING/FOLLOWING are allowed (as of version 3.25.0).

Example syntax:

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

SQLite’s window function support is comparable to PostgreSQL and MySQL, though some advanced features like RESPECT/IGNORE NULLS are not available.

FILTER clause

The FILTER clause is syntactic sugar that allows conditional aggregation. Example:

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

In SQLite, FILTER is supported for aggregate functions used with OVER clauses (since 3.25.0) but not for aggregates in GROUP BY queries; a CASE expression must be used instead.

INSERT … ON CONFLICT (upsert)

SQLite introduced the upsert syntax to handle primary‑key or unique‑constraint conflicts. Example:

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

If the parser cannot distinguish the ON keyword, adding a dummy WHERE TRUE clause resolves the ambiguity.

Rename column

SQLite added support for renaming a column with the standard syntax:

ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

Other updates

In 2018 SQLite also introduced various API changes; details are available on the official SQLite news page.