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

SQLite is an underrated database, often dismissed as a toy, but it is reliable, can handle TB‑scale data, though it lacks a network layer. This article explores SQLite’s latest SQL features from the past year.

SQLite is "just" a library, not a traditional server, so it may be unsuitable for some scenarios but is the optimal choice for many others. It claims to be the most widely deployed database engine, likely because it has no licensing restrictions; whenever developers need to store structured data in a file using SQL, SQLite should be the first choice.

SQLite’s SQL dialect is also very powerful. It supported the WITH clause four years before MySQL and recently added support for window functions, only five months later than MySQL.

The new SQL features added between versions 3.22.0 and 3.26.0 include:

Boolean literals and expressions

Window functions

FILTER clause

INSERT … ON CONFLICT ("Upsert")

Rename column

Various API updates

Boolean Literals and Expressions

SQLite accepts the type name Boolean but treats it as an integer, with true as 1 and false as 0. Since 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.

Example comparisons: WHERE c <> FALSE versus WHERE c IS NOT FALSE If c is NULL, the first expression yields UNKNOWN and the row is filtered out, while the second expression evaluates to TRUE and the row is kept. An equivalent explicit form is:

WHERE c <> FALSE
   OR c IS NULL

SQLite now supports boolean literals and predicates similarly to other open‑source databases, except it does not support IS [NOT] UNKNOWN (use IS [NOT] NULL instead).

Window Functions

SQLite 3.25.0 introduced window functions. The OVER clause is supported much like other databases, but the RANGE frame can only use CURRENT ROW or UNBOUNDED PRECEDING/FOLLOWING. PostgreSQL 11 later removed this limitation.

FILTER Clause

The FILTER clause, syntactic sugar for conditional aggregation, became available in SQLite 3.25.0 for aggregate functions that use an OVER clause, but not for those using GROUP BY. Example:

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

Because FILTER is not supported with GROUP BY, developers must continue to use CASE expressions for those cases.

INSERT … ON CONFLICT (UPSERT)

Since version 3.24.0 SQLite supports the UPSERT syntax, allowing an INSERT to handle primary‑key or unique‑constraint conflicts by either ignoring them or updating the existing row.

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

Because the parser cannot always distinguish the ON keyword, adding a harmless WHERE TRUE clause can resolve ambiguities:

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

Rename Column

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

ALTER TABLE … RENAME COLUMN … TO …

Other Updates

In 2018 SQLite also introduced several API changes. See the official SQLite news page for details.

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

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

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