In sqlite How to add column in table if same column is not exists in table

SQLite returns an error like "no such column: foo" if the table doesn't contain the column:

  select foo from yourTable limit 1

Also you can get the create-table statement:

 select sql from sqlite_master where tbl_name = 'YourTableName'

and then parse the result, looking for the column-name. I don't know of an elegant way to query the list of columns for a specified table, though one may exist.

Also if you attempt to do this:

alter table YourTable add column foo {column-def whatever it is}

you get an error from SQLite if the column already exists. You could trap that error too.

Finally you could do this:

  select sql from sqlite_master 
  where tbl_name = 'YOURTABLE' and sql like '%"foo" CHAR%';    -- or whatever type

and if the specified table contains the column which is surrounded by double-quotes in the query, and with the type you have specified, you will get a result, otherwise an empty set. Specifying the datatype ensures that your LIKE substring match occurs on a column-name.

There's no way (that I know of) to do it all in a single SQLite query. You must use application code to manage the If/Elseness.

Check if table exists or not:

select count(*) from sqlite_master where type = 'table' and name = MyTable';

Check if column exists in table or now

pragma table_info(thumbnail);

However, a better approach may be explicit database schema updates based on schema versions your application maintains (e.g. specific alter table statement to go from schema version 1 to 2):

pragma user_version;

It seems like that it is impossible to do checking if the column not exists and addindg the new column in one command, because Sqlite don't support "IF NOT EXISTS" for column. "IF NOT EXISTS" works only on table. Here is what I will do:

rev = ExecuteStatement("SELECT columnNamexx FROM tableNamexx limit 1;");

if(rev != SQLITE_OK){ // add col to table
    ExecuteStatement("ALTER TABLE tableNamexx ADD COLUMN columnNamexx INTEGER DEFAULT 0;");
}