SQLite in 2018: A state of the art SQL dialect

SQLite is an undervalued database—some people even believe it is a toy database that is not suitable for production use. In fact, SQLite is a very solid database that can handle terabytes of data, but it doesn’t have a network layer.

SQLite is “just” a library, not a server. That, of course, makes it inappropriate for some use cases, and at the same time it makes it the best fit for many other use cases. Really…many other use cases. SQLite even claims to be the most widely deployed and used database engine. I guess that is only possible because SQLite is in the public domain. SQLite is your go-to solution whenever you want to use SQL to store structured data in a file.

The SQL dialect of SQLite is also very strong. For example, it introduced the with clause four years before MySQL did. Lately, it has even added window functions—just five months after MySQL did the same.

This article covers the SQL enhancements that were added to SQLite in 2018, i.e. the new SQL features introduced in versions 3.22.0 through 3.26.0.

Contents:

1. Boolean Literals and Tests
2. Window Functions
3. Filter Clause
4. Insert … on conflict (“Upsert”)
5. Rename Column
6. Coming Next on Modern-SQL.com

Boolean Literals and Tests

SQLite has “fake” Boolean support: it accepts Boolean as a type name, but it treats it as an alias for an integer (very much like MySQL does). The truth values true and false are represented by the numerical values 1 and 0, respectively (like in C).

Starting with release 3.23.0, SQLite understands the keywords true and false as synonyms for the values 1 and 0 and supports the is [not] true|false test. The keyword unknown is generally not supported. You can use null instead because the values unknown and null are the same for Boolean values.

The literals true and false can greatly improve the readability of values and set clauses in insert and update statements.

The is [not] true|false test is useful because it has a different meaning than the corresponding comparison operations:

WHERE c <> FALSE

vs.

WHERE c IS NOT FALSE

If c is the null value, the result of the condition c <> false is unknown. As the where clause only accepts true values, but rejects false as well as unknown values, it will remove those rows from the result.

In contrast, the result of c is not false is also true if c is the null value. The second where clause will thus also accept rows where c has the null value.

Another way to select the same rows is to accept the null case separately.

WHERE c <> FALSE
   OR c IS NULL

This variant is longer and has some redundancy (c is mentioned twice). To make a long story short, the is not false test can be used to avoid such or … is null constructs. More about this in “Binary Decisions Based on Three-Valued Results”.

The support of Boolean literals and Boolean tests in SQLite is now close to that of other open source databases. The only gap is that SQLite does not support the is [not] unknown test (use is [not] null instead). Interestingly, these features are generally not available in the commercial products shown below.

1. Only true and false, not unknown. Use null instead
2. No is [not] unknown. Use is [not] null instead

Window Functions

SQLite 3.25.0 introduces window functions. If you know window functions, you also know that this is a big deal. If you don’t know window functions, learn how to use them! This article is not the right place to explain window functions, but trust me: it is the most important “modern” SQL feature at all.

SQLite’s support of the over clause is pretty close to that of other databases. The only notable limitation is that range frames don’t support numeric or interval distances (only current row and unbounded preceding|following). This is the same limitation SQL Server has, and PostgreSQL had at the time when SQLite 3.25.0 was released. In the meantime, PostgreSQL 11 has lifted this limitation.

1. No chaining of window definitions
2. No <distance> (only unbounded and current row)
3. No <distance> (only unbounded and current row) and no date and time types

The set of window function supported by SQLite is pretty much at a state of the art level. The main omissions (distinct in aggregates, width_bucket, respect|ignore nulls, and from first|last) are also missing in some other products.

1. DISTINCT aggregates not supported
2. Also without ORDER BY clause
3. No respect|ignore nulls
4. No negative offsets • Proprietary nulls treatment: lead(<expr>, 'IGNORE NULLS') (it’s a string argument)
5. No default possible (3rd argument) • No respect|ignore nulls
6. No negative offsets • No ignore nulls
7. No negative offsets
8. Proprietary nulls treatment: first_value(<expr> IGNORE NULLS) (no comma)
9. Proprietary nulls treatment: first_value(<expr>, 1, null, 'IGNORE NULLS') (it’s a string argument)
10. No ignore nulls
11. Proprietary nulls treatment: nth_value(<expr>, <off> IGNORE NULLS) (no comma) • No from last
12. No ignore nulls • No from last

Filter Clause

Even though the filter clause is just syntax sugar—you can easily use case expressions for the same result—I think it’s essential syntax sugar because it makes learning and understanding a lot easier.

Just look at the following select clauses. Which one is easier to understand?

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

vs.

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

This example pretty much summarizes what the filter clause does: it is a suffix for aggregate functions that conditionally removes rows before the aggregation. The pivot technique is the most common use case of the filter clause. That includes transforming attributes from the entity-attribute-value (EAV) model into columns. Learn more about it in “filter — Selective Aggregates”.

SQLite 3.25.0 introduced the filter clause for aggregate functions that use the over clause—not for aggregates that use group by. Unfortunately, that means that you still cannot use filter for the above mentioned use cases in SQLite. You have to revert to case expression as before. I truly hope that changes soon.

Insert … on conflict (“Upsert”)

SQLite 3.24.0 introduced the so-called “upsert”: an insert statement that allows graceful handling of primary key and unique constraint violations. You can choose to either ignore these errors (on conflict … do nothing) or update the existing rows (on conflict … do update …).

This is a proprietary SQL extensions, i.e. it is not part of the SQL standard and therefore gray in the matrix below. However, SQLite uses the same syntax as PostgreSQL for this feature.0 The standard offers the merge statement for this and other use cases.

Unlike PostgreSQL, SQLite has a problem with the following statement.

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

According to the documentation, the problem is that “the parser does not know if the token "ON" is part of a join constraint on the SELECT, or the beginning of the upsert-clause.” This can be resolved by adding another clause to the query, e.g. where true.

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

1. Also log errors for insert, update, delete, and merge (“DML error logging”)
2. On conflict must not immediately follow the from clause of a query. Add where true if needed

Rename Column

Another proprietary feature that SQLite introduced is the ability to rename columns in base tables.1 The SQL standard does not offer such functionality.2

SQLite follows the syntax commonly used by other products to rename columns:

ALTER TABLE … RENAME COLUMN … TO …

1. Read about sp_rename

Other News

Besides the SQL changes, there were also some API changes in SQLite in 2018. Please refer to the news section on sqlite.com for all details.

Coming Next on Modern-SQL.com

The next article about PostgreSQL 11 is already in the making. Follow modern-sql.com via Twitter, e-mail or RSS to stay updated.

If you’d like to learn more about modern SQL, have a look at my training in Vienna. Besides window functions (mentioned above) the training covers recursion, indexing, and greatly improves your understanding of the basic SQL concepts. The training is based on the current draft of my next book. Take a look now!