What New SQL Features Did SQLite Add in 2018?

Since SQLite 3.23.0 the keywords TRUE and FALSE are interpreted as integer literals 1 and 0. SQLite also supports the predicates IS [NOT] TRUE and IS [NOT] FALSE. The keyword UNKNOWN is not supported; NULL can be used instead.

-- rows where column c is false (0) or NULL are filtered out
WHERE c <> FALSE;

-- rows where c is true (1) or NULL are kept
WHERE c IS NOT FALSE;
-- equivalent to:
WHERE c <> FALSE OR c IS NULL;

Window Functions

SQLite 3.25.0 added support for window functions with the OVER clause. The syntax is compatible with most databases, but the RANGE frame is limited to CURRENT ROW or UNBOUNDED PRECEDING/FOLLOWING; numeric offsets are not allowed. This mirrors early implementations in SQL Server and PostgreSQL (the latter lifted the restriction in version 11).

FILTER Clause for Window Aggregates

From SQLite 3.25.0 aggregate functions used with OVER can include a FILTER (WHERE …) clause, providing conditional aggregation without a CASE expression. The clause is not yet supported for aggregates combined with GROUP BY, so CASE must still be used in that context.

Without FILTER:

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

With FILTER:

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

INSERT … ON CONFLICT (UPSERT)

SQLite 3.24.0 introduced the UPSERT syntax. When an INSERT would violate a PRIMARY KEY or UNIQUE constraint, the statement can either ignore the conflict or update the existing row.

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

If the parser cannot disambiguate the ON keyword after a FROM clause, inserting a harmless WHERE TRUE before the ON CONFLICT clause resolves the ambiguity:

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

Rename Column

SQLite provides a non‑standard but convenient syntax to rename a column without recreating the table:

ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

Other 2018 Changes

In 2018 SQLite also released several API updates; details are listed on the official SQLite news page: https://www.sqlite.org/news.html.