SQLite 2018: New SQL Features – Booleans, Window Functions, Upserts & More

SQLite is frequently underrated as a "toy" database, yet it is a reliable engine capable of handling terabytes of data without a network layer. This article reviews the SQL features added to SQLite during 2018, covering versions 3.22.0 through 3.26.0.

New Features Overview

Boolean literals and checks

Window functions

FILTER clause

INSERT … ON CONFLICT ("Upsert")

Rename column

Boolean Literals and Checks

SQLite treats the Boolean type name as an integer, mapping true to 1 and false to 0. Since version 3.23.0 the keywords true and false are recognized, and the predicates IS [NOT] TRUE|FALSE are supported. The keyword UNKNOWN is not supported; NULL should be used instead.

Example comparisons: WHERE c <> FALSE and WHERE c IS NOT FALSE If c is NULL, c <> FALSE yields UNKNOWN and the row is filtered out, whereas c IS NOT FALSE evaluates to TRUE and the row is retained. An equivalent explicit form is: WHERE c <> FALSE OR c IS NULL SQLite’s support for Boolean literals now matches most other open‑source databases, with the only missing feature being IS [NOT] UNKNOWN, which can be simulated with IS [NOT] NULL.

Window Functions

SQLite 3.25.0 introduced window functions, aligning its OVER clause support with 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, a restriction shared with SQL Server and PostgreSQL at the time of release.

Additional constraints include lack of support for ORDER BY within the frame, disallowing negative offsets, and no built‑in handling of IGNORE NULLS or RESPECT NULLS in functions such as LEAD or FIRST_VALUE.

FILTER Clause

The FILTER clause acts as syntactic sugar for conditional aggregation, making queries more readable. For example:

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

can be rewritten as:

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

SQLite supports FILTER on aggregate functions that use an OVER clause starting with version 3.25.0, but it does not yet support FILTER on aggregates used with GROUP BY. Consequently, developers must still rely on CASE expressions for that scenario.

INSERT … ON CONFLICT ("Upsert")

Since version 3.24.0 SQLite offers an upsert mechanism via INSERT … ON CONFLICT, allowing either ignore or update actions when a primary‑key or unique‑constraint violation occurs. The syntax mirrors PostgreSQL’s:

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 versus an upsert clause, adding a harmless WHERE TRUE before the ON CONFLICT can resolve ambiguities.

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, enabling column renaming directly within the base table—a feature not available in standard SQL.

ALTER TABLE my_table RENAME COLUMN old_name TO new_name;

Other 2018 Changes

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