SQLite error "no such column": usually missing quotes, plus 4 other causes

You want the visitors who went into the room called Vault, so you write:

SELECT name FROM visitors WHERE room = Vault;

and SQLite answers:

Error: no such column: Vault

But Vault isn't supposed to be a column. It's the value you're looking for. That's exactly the problem, and the fix is two small marks.

Try it yourself

Here's a tiny table to run in any SQLite tool, such as DB Browser for SQLite or the SQL editor in a Craftstack session:

CREATE TABLE visitors (id INTEGER PRIMARY KEY, name TEXT, room TEXT, entered TEXT);
INSERT INTO visitors (name, room, entered) VALUES
  ('Ava Brooks', 'Lobby', '21:40'),
  ('Ben Carter', 'Vault', '22:15'),
  ('Cleo Diaz', 'Vault', '22:50'),
  ('Dan Evans', 'Cafe', '23:05');

Why SQLite looks for a column

Apart from SQL's own keywords like SELECT and WHERE, a word without quotes is a name: the name of a table, a column or a function. So in WHERE room = Vault, SQLite reads Vault the same way it reads room, as a column, and compares the two columns. There's no column called Vault, so it stops.

Text values, the actual words stored in the table, go in single quotes:

SELECT name FROM visitors WHERE room = 'Vault';

Ben Carter
Cleo Diaz

Numbers don't need quotes (WHERE id = 2), and neither do column names. Text always does.

The tool you use may word the start of the message differently. Python's sqlite3 module, for example, raises sqlite3.OperationalError: no such column: Vault. The important part is the same: no such column, followed by the word SQLite couldn't find.

Four other causes

A typo in a column name

SELECT nmae FROM visitors;

Error: no such column: nmae

The word at the end of the message is your clue: it's exactly what you typed. Compare it with the real column names letter by letter.

A column that's in another table

SELECT name, coat FROM visitors;

Error: no such column: coat

Maybe coat exists, but in a different table. A query can only use columns from the tables in its FROM part. To use columns from two tables, you join them with JOIN, which is a topic of its own.

A table alias that wasn't declared

Short names for tables, like v for visitors, only exist if you declare them in the FROM part:

SELECT v.name FROM visitors WHERE room = 'Vault';

Error: no such column: v.name

Add the alias after the table name, and use it consistently:

SELECT v.name FROM visitors AS v WHERE v.room = 'Vault';

A column added after the table was created

This one catches people who build a small app. The code starts with CREATE TABLE IF NOT EXISTS, and later you add a column to it:

CREATE TABLE IF NOT EXISTS visitors (id INTEGER PRIMARY KEY, name TEXT, room TEXT, entered TEXT);

If the database file already has a visitors table from before, without entered, this line does nothing: the table exists, so it isn't created again. The next SELECT entered FROM visitors fails with no such column: entered, even though the column is right there in your code.

Add the column to the existing table:

ALTER TABLE visitors ADD COLUMN entered TEXT;

Or, while you're still practising and the data doesn't matter, delete the database file (or DROP TABLE visitors;) and let the code create it again.

Errors that look alike

A few other messages come from the same kind of mix-up between names and values:

• no such table: visitor: the same problem one level up, a misspelled table name (here visitor instead of visitors), or a table that was never created in this database file.
• table visitors has no column named nmae: what SQLite says when the misspelled column is in an INSERT, like INSERT INTO visitors (nmae, room) VALUES ('Eve Fox', 'Lobby');.
• ambiguous column name: name: the opposite problem. After a JOIN, two tables both have a column called name, and SQLite doesn't know which one you mean. Put the table name in front: SELECT visitors.name, rooms.floor FROM visitors JOIN rooms ON visitors.room = rooms.name;.

The same error in PostgreSQL

If you move to PostgreSQL, the missing quotes give ERROR: column "vault" does not exist. Notice the lower-case vault: PostgreSQL turns names without quotes into lower case, so the message doesn't even show the word exactly as you typed it. And "Vault" in double quotes, which SQLite quietly accepts as text, is a column name in PostgreSQL: ERROR: column "Vault" does not exist. Single quotes work in both.

How to see which columns a table has

In SQLite, PRAGMA table_info lists the columns of a table:

PRAGMA table_info(visitors);

cid | name    | type    | notnull | dflt_value | pk
0   | id      | INTEGER | 0       |            | 1
1   | name    | TEXT    | 0       |            | 0
2   | room    | TEXT    | 0       |            | 0
3   | entered | TEXT    | 0       |            | 0

Most tools also show the columns in a sidebar. Check the list before you go hunting for anything more complicated.

A quick checklist

1. Read the word at the end of the message: that's what SQLite tried to use as a column.
2. Is it a value, like a room, a name or a color? Put it in single quotes.
3. Is it meant to be a column? Check the spelling against the table's columns with PRAGMA table_info.
4. Is the column in another table? Add that table with a JOIN.
5. Does it start with a short name like v.? Declare that alias in the FROM part.
6. Did you add the column to your code later? Add it to the existing table with ALTER TABLE ... ADD COLUMN.

Once it works, you can keep adding conditions with AND:

SELECT name FROM visitors WHERE room = 'Vault' AND entered >= '22:30';

Cleo Diaz

New to SQL and wondering how it compares with other languages? See Which programming language should you learn first?