How AUTOINCREMENT Works in SQLite

In SQLite, an AUTOINCREMENT column is one that uses an automatically incremented value for each row that’s inserted into the table.

There are a couple of ways you can create an AUTOINCREMENT column:

  • You can create it implicitly when you define the column as INTEGER PRIMARY KEY.
  • You can create it explicitly with the AUTOINCREMENT keyword. One downside of this method is that it uses extra CPU, memory, disk space, and disk I/O overhead.

Both methods cause the column to use an incrementing value each time a new row is inserted with NULL in that column.

However, there are some subtle differences between how each method works.

Continue reading

Tweak your Avg() Results in SQLite with the DISTINCT Keyword

If you know about the avg() function in SQLite, you’re probably aware that it returns the average of all non-NULL X within a group.

But did you know you can add the DISTINCT keyword to this function?

If you add the DISTINCT keyword, avg() will calculate its results based on distinct values only. This is essentially the same as removing duplicate values and then calculating the average on the remaining values.

Continue reading

2 Ways to List the Tables in an SQLite Database

Here are two ways to return a list of tables in all attached databases in SQLite.

The first method returns all tables and views for all attached databases.

The second method gives you the option of returning both tables and views, or just tables, but only for the primary database.

Update Dec 2021: Since writing this article, SQLite has introduced another option, which I’ve listed as a bonus third option at the end of this article.

Continue reading