SQLite: no such column

What it means

Before running a statement, SQLite looks up every column it names in the tables of the FROM clause, or in the table you’re inserting into or updating. If one isn’t there, preparing the statement fails with SQLITE_ERROR (code 1) and no such column: <name>, and nothing runs. A qualified name keeps its prefix in the message: no such column: o.totl.

An INSERT that lists a column the table doesn’t have words it differently: table customers has no column named phone. The causes and fixes are the same.

Column names aren’t case-sensitive in SQLite, so NAME finds name. When this error appears, the column really isn’t where SQLite looked.

Common causes

A typo, or a name from another table: nmae for name, user_id for customer_id.
The column isn’t in this file yet. Your code expects a column that a migration adds, but the migration hasn’t run against the database you opened, or you opened a different file (see no such table for how that happens).
Text in double quotes. In SQL, single quotes are for text ('Ada') and double quotes are for names ("order"). WHERE name = "Ada" asks for a column called Ada. Some SQLite builds then fall back to treating it as text, others refuse.
A table name after you gave it an alias. Once you write FROM customers c, the table is called c in that query, and customers.name fails.
rowid on a table that has none: a WITHOUT ROWID table, or a view.

How to fix it

Check the table’s columns

SELECT name, type FROM pragma_table_info('customers');

id|INTEGER
name|TEXT
email|TEXT
created_at|TEXT

In the sqlite3 shell, .schema customers shows the whole CREATE TABLE. Compare with the name in the message: spelling, underscores, singular or plural.

Add the column, or run the migration

If the column should exist, run your migrations against this file, or add it yourself:

ALTER TABLE customers ADD COLUMN phone TEXT;

Some columns can’t be added this way (a NOT NULL column without a default, a UNIQUE column); see Cannot add a NOT NULL column with default value NULL.

Put text in single quotes

SELECT * FROM customers WHERE name = 'Ada';

To put a single quote inside the text, double it: 'O''Brien'. Keep double quotes for names that need them, such as reserved words and names with spaces: SELECT "order", "unit price" FROM items;.

Whether "Ada" falls back to text depends on how SQLite was built and configured. The sqlite3 shell from sqlite.org stopped accepting it in 3.41.0, but macOS’s /usr/bin/sqlite3 (3.51.0) still did in our test. The fallback is risky where it works: when the quoted word matches a real column, SQLite uses the column. WHERE email = "email" compares the column with itself and returns every row. Turn the fallback off to catch these (.dbconfig dqs_dml off in the shell, SQLITE_DBCONFIG_DQS_DML in C).

Use the alias

Once a table has an alias, use it everywhere in that query:

SELECT c.name, o.total
FROM customers c JOIN orders o ON o.customer_id = c.id;

Select the key instead of rowid

A WITHOUT ROWID table has no rowid; select its primary key. A view has none either; select a column of the table underneath.

Reproduce it

macOS /usr/bin/sqlite3, SQLite 3.51.0, with customers (id, name, email, created_at) and orders (id, customer_id, total, status) in shop.db:

sqlite3 shop.db "SELECT id, nmae FROM customers;"

Error: in prepare, no such column: nmae
  SELECT id, nmae FROM customers;
             ^--- error here

The shell adds “Error: in prepare,” and points at the name; SQLite’s message is no such column: nmae. The other forms:

Error: in prepare, no such column: o.totl
Error: in prepare, no such column: phone
Error: in prepare, table customers has no column named phone
Error: in prepare, no such column: customers.name
Error: in prepare, no such column: rowid

Those are a mistyped column after an alias, UPDATE customers SET phone = …, INSERT INTO customers (name, phone) …, SELECT customers.name FROM customers c, and rowid on a WITHOUT ROWID table.

WHERE name = "Ada" returned Ada’s row. After .dbconfig dqs_dml off, the same query failed:

Error: in prepare, no such column: "Ada" - should this be a string literal in single-quotes?

Through Python’s sqlite3 module (SQLite 3.53.4), the typo raised sqlite3.OperationalError: no such column: nmae, with sqlite_errorname SQLITE_ERROR.

In Inlet

The query editor completes column names from the live schema, which catches most typos before you run anything. When SQLite rejects a name, Inlet shows the error at the position SQLite reports.

no such column