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

SQLite is often underestimated as a "toy" database, yet it is a highly reliable, file‑based engine capable of handling terabytes of 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, especially when developers need to store structured data in a file using SQL. WHERE c IS NOT FALSE Since version 3.23.0 SQLite treats the keywords true and false as integers 1 and 0, supporting IS [NOT] TRUE|FALSE predicates while the UNKNOWN keyword is no longer supported; NULL can be used instead.

WHERE c <> FALSE
   OR c IS NULL

This predicate is useful because WHERE only keeps rows where the expression evaluates to true, filtering out false or unknown results; using IS NOT FALSE also includes rows where the column is NULL.

1. Boolean Literals and Checks

SQLite stores Boolean values as integers (1 for true, 0 for false), similar to MySQL and C. Literal true and false improve readability in INSERT and UPDATE statements.

2. Window Functions

SQLite added support for the OVER clause, bringing its window‑function capabilities close to those of other databases. The only notable limitation is that RANGE frames cannot use numeric or interval distances—only CURRENT ROW and UNBOUNDED PRECEDING/FOLLOWING are allowed (the same restriction existed in SQLite 3.25.0, PostgreSQL 11 later removed it).

3. FILTER Clause

The FILTER clause, a syntactic sugar for conditional aggregation, allows expressions such as:

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

SQLite supports FILTER on aggregate functions with an OVER clause starting from version 3.25.0, but not on aggregates used with GROUP BY, requiring the traditional CASE expression.

4. Upsert (INSERT … ON CONFLICT)

SQLite introduced the “upsert” syntax in version 3.24.0, allowing elegant handling of primary‑key or unique‑constraint conflicts:

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

If the parser cannot distinguish the ON keyword, adding a dummy WHERE TRUE clause resolves the ambiguity.

5. Rename Column

SQLite added a non‑standard but widely used syntax to rename a column:

ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

This mirrors the syntax of other major databases, even though the SQL standard does not define such a feature.

6. Other 2018 Changes

Beyond SQL syntax, SQLite also introduced several API updates in 2018; details can be found on the official SQLite news page.

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