SQLite 2018 SQL Features: Window Functions, UPSERT, FILTER Clause & Boolean Logic

SQLite is often underestimated as a toy database, but it reliably handles terabyte-scale data and is the most widely deployed database engine due to its public-domain licensing. Whenever developers need SQL-based structured storage in a file, SQLite should be the first choice.

The article surveys SQLite's SQL dialect improvements added during 2018 (versions 3.22.0 through 3.26.0), focusing on six areas: boolean literals and tests, window functions, the FILTER clause, UPSERT (INSERT … ON CONFLICT), column renaming, and a note on API changes.

01 Boolean Literals and Tests

SQLite treats Boolean as an integer type (true = 1, false = 0), similar to MySQL and C. Starting with version 3.23.0, the keywords true and false became available as literals, and the predicates IS [NOT] TRUE | FALSE were introduced. The keyword UNKNOWN is not supported; use IS [NOT] NULL instead.

These literals improve readability in INSERT and UPDATE statements. The IS [NOT] TRUE | FALSE test differs from comparison operators because it handles three-valued logic correctly: WHERE c <> FALSE If c is NULL, the result is UNKNOWN, and the row is filtered out (WHERE keeps only TRUE). By contrast: WHERE c IS NOT FALSE returns TRUE when c is NULL, so the row is retained. The equivalent verbose form would be WHERE c <> FALSE OR c IS NULL, which repeats the column reference. SQLite's boolean support now matches other open-source databases except for the missing IS [NOT] UNKNOWN (use IS [NOT] NULL). Notably, some commercial products still lack these features.

02 Window Functions

Window functions are described as the most important modern SQL feature. SQLite's OVER clause support closely matches other databases. The only notable limitation is that RANGE frame boundaries do not accept numeric or interval offsets — only CURRENT ROW and UNBOUNDED PRECEDING|FOLLOWING. At the time of SQLite 3.25.0, SQL Server and PostgreSQL shared this limitation; PostgreSQL 11 later removed it.

A comparison matrix shows SQLite's window-function gaps relative to the SQL standard and other products: missing DISTINCT inside aggregates, WIDTH_BUCKET, RESPECT|IGNORE NULLS, and FROM FIRST|LAST. Several specific functions ( LEAD, LAG, FIRST_VALUE, LAST_VALUE, NTH_VALUE, NTILE) have restrictions such as disallowing negative offsets or lacking IGNORE NULLS support. Many of these gaps also exist in other major databases.

03 FILTER Clause

The FILTER clause is syntactic sugar that makes aggregate queries easier to read and learn. It acts as a postfix on an aggregate function, filtering rows before aggregation. The classic use case is pivoting (e.g., turning entity-attribute-value rows into columns).

Example without FILTER (using CASE):

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

With FILTER (cleaner):

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

SQLite 3.25.0 added FILTER support for window aggregates (with OVER), but not yet for grouped aggregates (with GROUP BY), so the pivot example still requires CASE expressions.

04 UPSERT (INSERT … ON CONFLICT)

Version 3.24.0 introduced INSERT … ON CONFLICT ("UPSERT"), a non-standard extension that follows PostgreSQL's syntax. It handles primary-key or unique-constraint conflicts by either ignoring them ( DO NOTHING) or updating the existing row ( DO UPDATE SET …). The standard SQL alternative is MERGE.

A parser ambiguity arises when the INSERT source is a SELECT without a WHERE clause:

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

The parser cannot tell whether ON starts a join condition or the UPSERT clause. The workaround is to add a dummy WHERE true:

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

05 Renaming Columns

SQLite added ALTER TABLE … RENAME COLUMN … TO …, a vendor-specific feature not in standard SQL (though simulatable via updatable views or derived columns). The syntax follows common practice in other products. The change applies only to base tables created with CREATE TABLE; derived table column names can be renamed in the SELECT, FROM, or WITH clause.

06 Other Changes

Besides SQL syntax, SQLite 2018 releases included API changes. Details are on sqlite.org/news.html.

07 Footnotes

0: SQLite typically follows PostgreSQL syntax; Richard Hipp calls this "What Would PostgreSQL Do" (WWPD).

1: Base tables are those created by CREATE TABLE; derived table column names can be changed in the query itself.

2: Standard SQL may simulate column renaming via updatable views or derived columns.

Source: https://modern-sql.com/blog/2019-01/sqlite-in-2018