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

Question

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

Using ALTER TABLE I am able to create a new column but want to know how to check whether that column already exists in the table or not?

Answer 1

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.

Answer 2

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;