SQLite: Check whether table exists

Question: Before writing data into a table or reading data from a table, we would like to check whether the table generally exists to prevent reading of writing errors.

Solution: The names of all existing tables are listed in "sqlite_master" and can be retried from there. Here is an example of how to check the existence of the table "tab".

SELECT name FROM sqlite_master WHERE type='table' AND name='tab';

If the result of this query is empty, we know that the table does not exist.

Important: Temporary tables can not be retried from "sqlite_master" because they are listed in "sqlite_temp_master". If we are interested in temporary tables, we can just replace "sqlite_master" with "sqlite_temp_master" in the query above.

Alternative suggestion: For some applications, other solutions are easier to implement. For example, imagine that one of our programs needs some tables in our database. When starting the program, it is not sure whether the program has been started before and if the appropriate tables are already created. Therefore, we would like to check whether a specific table is already existing so that we can only create the new table if not.

CREATE TABLE IF NOT EXISTS 'tab' (...);

For this case, the statement "CREATE TABLE IF NOT EXISTS" can be the simpler way. This statement is equivalent to the "CREATE TABLE"-statement but it is supplemented by "IF NOT EXISTS". That means that the new table is only created if it is not existing yet. This saves you the detour of checking this before.