# 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:

```sql
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:

```sql
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:

```sql
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.

**What about double quotes?** SQLite often accepts `"Vault"` too, but only as a fallback: double quotes are meant for names, and other databases such as PostgreSQL read `"Vault"` as a column. Use single quotes for text and your queries will work everywhere.

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

```sql
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

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
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;`.

Once you start counting and grouping rows, a different family of messages appears: [SQL GROUP BY errors: "misuse of aggregate" and "must appear in the GROUP BY clause"](https://craftstackacademy.com/blog/sql-group-by-misuse-of-aggregate-must-appear-in-group-by).

## 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:

```sql
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`:

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

```
Cleo Diaz
```

The same "read the last word" habit works for errors in other languages too: [How to Read an Error Message: A 3-Step Method for Python, Java, JavaScript and SQL](https://craftstack.hashnode.dev/how-to-read-an-error-message-a-3-step-method-for-python-java-javascript-and-sql).

New to SQL and wondering how it compares with other languages? See [Which programming language should you learn first?](https://craftstackacademy.com/blog/which-programming-language-to-learn-first)

Our free SQL trial, "The Museum Mystery", is built around a query just like this one: a diamond is missing from the Vault, and you narrow the visitor log down, clue by clue, to the thief. If you meet "no such column" on the way, the AI tutor lets you find the missing quotes yourself. One free 20-minute session, nothing to install: [craftstackacademy.com/trial](https://craftstackacademy.com/trial). It leads into our [Databases & SQL course](https://craftstackacademy.com/courses/sql).

*This article was first published on the [Craftstack Academy blog](https://craftstackacademy.com/blog/sqlite-no-such-column-error).*