In sqlite How to add a column to a table if the same column does not exist in the table

Question

How to add a column to a SQLite table if and only if the same column does not exist in the table?

With ALTER TABLEI can create a new column, but want to know how to check if this column exists in the table or not?

Answer 1

You can view table columns using ".schema tableName"

Answer 2

SQLite returns an error, for example, "no such column: foo" if the table does not contain a column:

  select foo from yourTable limit 1

You can also get the create-table statement:

 select sql from sqlite_master where tbl_name = 'YourTableName'

, . , .

, :

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

SQLite, . .

, :

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

, , , , - . LIKE .

Answer 3

( ) SQLite. If/Elseness.

, :

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

,

pragma table_info(thumbnail);

However, the best approach might be to explicitly update the database schema based on the versions of the schemas supported by your application (for example, the specific alter table command to go from schema 1 to 2):

pragma user_version;

Answer 4

It seems impossible to check if a column exists and add a new column to a single command, because Sqlite does not support “IF NOT EXISTS” for the column. "IF DOES NOT EXIST" only works on the 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;");
}