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

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

Because SQLite is a library rather than a traditional server, it may be unsuitable for some scenarios but is the optimal choice for many others, making it the most widely deployed database engine without licensing restrictions.

Its SQL dialect is powerful, having supported WITH statements four years before MySQL and recently adding window function support, only five months later than MySQL.

New SQL Features Added in SQLite 3.22.0–3.26.0 (2018)

Boolean literals and checks

Window functions

FILTER clause

INSERT … ON CONFLICT ("Upsert")

Rename column

Further updates on Modern‑SQL.com

Boolean Literals and Checks

SQLite treats the type name BOOLEAN as an integer, mapping TRUE to 1 and FALSE to 0. Starting with version 3.23.0, the keywords TRUE and FALSE are recognized, and IS [NOT] TRUE|FALSE predicates are supported. The keyword UNKNOWN is no longer supported; use NULL instead.

Using literal TRUE / FALSE in INSERT and UPDATE statements improves readability. WHERE c <> FALSE versus 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 includes the row.

An alternative handling of NULL values is:

WHERE c <> FALSE
   OR c IS NULL

Window Functions

SQLite 3.25.0 introduced window functions. The OVER clause is supported similarly to other databases, but the RANGE frame only allows CURRENT ROW and UNBOUNDED PRECEDING/FOLLOWING. PostgreSQL 11 later removed this limitation.

Key observations:

No support for numeric or interval RANGE values.

Only UNBOUNDED and CURRENT ROW are allowed.

Some advanced features such as IGNORE NULLS and FROM LAST are not supported.

FILTER Clause

The FILTER clause is syntactic sugar that makes aggregate expressions clearer. Example:

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

can be written as

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

SQLite supports FILTER in aggregate functions that use OVER (since 3.25.0) but not in aggregates with GROUP BY, requiring the traditional CASE expression workaround.

INSERT … ON CONFLICT ("Upsert")

SQLite 3.24.0 added upsert support, allowing INSERT statements to handle primary‑key or unique‑constraint conflicts either by ignoring them or updating the existing row.

SQLite follows PostgreSQL syntax for this feature:

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

Because the parser cannot always distinguish the ON keyword, a WHERE TRUE clause can be added as a workaround:

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

Rename Column

SQLite introduced a non‑standard ALTER TABLE … RENAME COLUMN … TO … statement, allowing column renaming directly.

ALTER TABLE … RENAME COLUMN … TO …

Other 2018 Updates

Beyond SQL syntax, SQLite also introduced several API changes in 2018; details are available on the official SQLite news page.

Footnotes

SQLite often mirrors PostgreSQL syntax; Richard Hipp refers to this as “What Would PostgreSQL Do”.

Base tables are created with CREATE TABLE; derived tables from SELECT can have column names altered via SELECT, FROM, or WITH clauses.

Column renaming can be simulated via updatable views or derived columns.