In this article, we will talk about the “no such column” SQLite error, that anyone can have. We can be absolutely sure that the column exists, but the SQLite cannot see it. Here we will find the solution to fix this problem.
Reasons for the SQLite “no such column” error
This error occurs when there are spaces or characters other than A-Z and 0-9 in our column headers.
String literals in SQL are denoted by single quotes (‘). Without them, the string will be considered as the name of the object.
If we want to tell SQLite that our header is a column header, we need to use double or back quotes.
SQLite can tell us that the column doesn’t exist when it simply can’t recognize the column name, so when there is no column name there is no column.
Examples and solutions
Let’s imagine that we have a database with a table that contains a column with name “Some Column”.
Our table is:
CREATE TABLE Test_Table1 ( "Some Column" VARCHAR(50) );
We need to select it, so we use a script:
SELECT Some Column FROM Test_Table1;
But we are unable to do this action, so we receive a message:
[SQLITE_ERROR] SQL error or missing database (no such column: Some)
Figure 1. The SQLite error
This happens because of the space in the name of our column. To make SQLite see that Some Column is a name we use double quotes.
Now it will work properly:
SELECT "Some Column" FROM Test_Table1;
Figure 2. The column name with a space
The same error can occur when the name of our column contains, for example, a dot, like Salary.Month.
This is our table:
CREATE TABLE Test_Table2 ( "Salary.Month" FLOAT );
So when we try to use this script:
SELECT Salary.Month FROM Test_Table2;
We will also receive an error.
Figure 3. The SQLite error
The situation is the same as with a space, that is why we also need to use double quotes to make the SQLite understand the name.
SELECT "Salary.Month" FROM Test_Table2;
Figure 4. The column name with a dot
The last example will show that the spaces at the end of the word can also cause an error and, what is more, it is harder to find a space in the column name when it is at the end.
Our table is:
CREATE TABLE Test_Table3 ( "What's your name? " VARCHAR(50) );
Let’s select the column:
SELECT "What's your name?" FROM Test_Table3;
This query will also return the error because we haven’t included the space in the double quotes.
We must be very careful not to forget about the space at the end of the column name because later we can spend a lot of time looking for the cause of such a simple mistake.
This is the right query:
SELECT "What's your name? " FROM Test_Table3;
Figure 5. The column name with a space at the end
Instant livechat to an Expert!
Most of the time, the problem you will need to solve will be more complex than a simple method. If you want to save hours of research and frustration, try our live SQLExpert service!
Our SQLQuerychat Experts are available 24/7 to answer any questions you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.