ALTER TABLE ADD COLUMN IF NOT EXISTS in SQLite

Question

We ve recently had the need to add columns to a few of our existing SQLite database tables   This can be done with ALTER TABLE ADD COLUMN   Of course  if the table has already been altered  we want to leave it alone   Unfortunately  SQLite doesn t support an IF NOT EXISTS clause on ALTER TABLE   Our current workaround is to execute the ALTER TABLE statement and ignore any  duplicate column name  errors  just like this Python example  but in C      However  our usual approach to setting up database schemas is to have a  sql script containing CREATE TABLE IF NOT EXISTS and CREATE INDEX IF NOT EXISTS statements  which can be executed using sqlite3 exec or the sqlite3 command-line tool   We can t put ALTER TABLE in these script files because if that statement fails  anything after it won t be executed   I want to have the table definitions in one place and not split between  sql and  cpp files   Is there a way to write a workaround to ALTER TABLE ADD COLUMN IF NOT EXISTS in pure SQLite SQL

User · Answer

threre is a method of PRAGMA is table info table name   it returns all the information of table   Here is implementation how to use it for check column exists or not       public boolean isColumnExists  String table  String column             boolean isExists   false          Cursor cursor           try                          cursor   db rawQuery  PRAGMA table info    table       null               if  cursor    null                    while  cursor moveToNext                          String name   cursor getString cursor getColumnIndex  name                         if  column equalsIgnoreCase name                             isExists   true                          break                                                                    finally               if  cursor    null  amp  amp   cursor isClose                    cursor close                        return isExists          You can also use this query without using loop   cursor   db rawQuery  PRAGMA table info    table     where name       column  null

User · Answer

One workaround is to just create the columns and catch the exception error that arise if the column already exist  When adding multiple columns  add them in separate ALTER TABLE statements so that one duplicate does not prevent the others from being created   With sqlite-net  we did something like this  It s not perfect  since we can t distinguish duplicate sqlite errors from other sqlite errors   Dictionary lt string  string gt  columnNameToAddColumnSql   new Dictionary lt string  string gt                   Column1            ALTER TABLE MyTable ADD COLUMN Column1 INTEGER                        Column2            ALTER TABLE MyTable ADD COLUMN Column2 TEXT            foreach  var pair in columnNameToAddColumnSql        string columnName   pair Key      string sql   pair Value       try               this DB ExecuteNonQuery sql             catch  System Data SQLite SQLiteException e                 log Warn e  string Format  Failed to create column   0    Most likely it already exists  which is fine    columnName

User · Answer

SQLite also supports a pragma statement called  table info  which returns one row per column in a table with the name of the column  and other information about the column    You could use this in a query to check for the missing column  and if not present alter the table   PRAGMA table info foo table name    http   www sqlite org pragma html pragma table info

User · Answer

I took the answer above in C   Net  and rewrote it for Qt C    not to much changed  but I wanted to leave it here for anyone in the future looking for a C   ish  answer       bool MainWindow  isColumnExisting QString  amp table  QString  amp columnName        QSqlQuery q       try           if q exec  PRAGMA table info    table                    while  q next                      QString name   q value  name   toString                         if  columnName toLower      name toLower                        return true                       catch exception           return false            return false

User · Answer

All these answers are fine if you execute one line at a time  However  the original question was to input a sql script that would be executed by a single db execute and all the solutions   like checking to see if the column is there ahead of time   would require the executing program either have knowledge of what tables and columns are being altered added or do pre-processing and parsing of the input script to determine this information   Typically you are not going to run this in realtime or often  So the idea of catching an exception is acceptable and then moving on   Therein lies the problem   how to move on  Luckily the error message gives us all the information we need to do this  The idea is to execute the sql if it exceptions on an alter table call  we can find the alter table line in the sql and return the remaining lines and execute until it either succeeds or no more matching alter table lines can be found   Heres some example code where we have sql scripts in an array  We iterate the array executing each script  We call it twice to get the alter table command to fail but the program succeeds because we remove the alter table command from the sql and re-execute the updated code     bin sh   the next line restarts using wish    exec  opt usr8 6 3 bin tclsh8 6   quot  0 quot    1  quot    quot   foreach pkg  sqlite3         if     catch  package require     pkg   err      0         puts stderr  quot Unable to find package  pkg n err n     adjust your auto path  quot           array set sqlArray       1       CREATE TABLE IF NOT EXISTS Notes                         id INTEGER PRIMARY KEY AUTOINCREMENT                        name text                        note text                        createdDate integer 4  DEFAULT   cast  strftime   s    now   as int                             updatedDate integer 4  DEFAULT   cast  strftime   s    now   as int                                   CREATE TABLE IF NOT EXISTS Version                           id INTEGER PRIMARY KEY AUTOINCREMENT                          version text                          createdDate integer 4  DEFAULT   cast  strftime   s    now   as int                               updatedDate integer 4  DEFAULT   cast  strftime   s    now   as int                                    INSERT INTO Version version  values  1 0              2       CREATE TABLE IF NOT EXISTS Tags           id INTEGER PRIMARY KEY AUTOINCREMENT          name text          tag text          createdDate integer 4  DEFAULT   cast  strftime   s    now   as int               updatedDate integer 4  DEFAULT   cast  strftime   s    now   as int                     ALTER TABLE Notes ADD COLUMN dump text      INSERT INTO Version version  values  2 0              3       ALTER TABLE Version ADD COLUMN sql text      INSERT INTO Version version  values  3 0               create db command   use in memory database for demonstration purposes sqlite3 db  memory   proc createSchema   sqlArray         upvar  sqlArray sql       execute each sql script in order      foreach version  lsort -integer  array names sql           set cmd  sql  version      set ok 0     while     ok  amp  amp   string length  cmd                 try           db eval  cmd         set ok 1        it succeeded if we get here           on error   err backtrace             if    regexp  duplicate column name    a-zA-Z0-9     string trim  err   match columnname                   puts  quot Error    err     trying again quot               set cmd  removeAlterTable  cmd  columnname             else               throw DBERROR  quot  err n backtrace quot                                      return sqltext with alter table command with column name removed   if no matching alter table line found or result is no lines then   returns  quot  quot  proc removeAlterTable   sqltext columnname         set mode skip     set result  list      foreach line  split  sqltext  n         if    string first  quot alter table quot   string tolower  string trim  line       gt   0             if    string first  columnname  line               set mode add         continue                      if    mode eq  quot add quot              lappend result  line                 if    mode eq  quot skip quot          puts stderr  quot Unable to find matching alter table line quot      return  quot  quot        elseif    llength  result             return   join  result  n         else       return  quot  quot                          proc printSchema           db eval   select   from sqlite master   x       puts  quot Table   x tbl name  quot      puts  quot  x sql  quot      puts  quot ------------- quot          createSchema sqlArray printSchema   run again to see if we get alter table errors  createSchema sqlArray printSchema  expected output Table  Notes CREATE TABLE Notes                         id INTEGER PRIMARY KEY AUTOINCREMENT                        name text                        note text                        createdDate integer 4  DEFAULT   cast  strftime   s    now   as int                             updatedDate integer 4  DEFAULT   cast  strftime   s    now   as int                              dump text  ------------- Table  sqlite sequence CREATE TABLE sqlite sequence name seq  ------------- Table  Version CREATE TABLE Version                           id INTEGER PRIMARY KEY AUTOINCREMENT                          version text                          createdDate integer 4  DEFAULT   cast  strftime   s    now   as int                               updatedDate integer 4  DEFAULT   cast  strftime   s    now   as int                               sql text  ------------- Table  Tags CREATE TABLE Tags           id INTEGER PRIMARY KEY AUTOINCREMENT          name text          tag text          createdDate integer 4  DEFAULT   cast  strftime   s    now   as int               updatedDate integer 4  DEFAULT   cast  strftime   s    now   as int                ------------- Error   duplicate column name  dump     trying again Error   duplicate column name  sql     trying again Table  Notes CREATE TABLE Notes                         id INTEGER PRIMARY KEY AUTOINCREMENT                        name text                        note text                        createdDate integer 4  DEFAULT   cast  strftime   s    now   as int                             updatedDate integer 4  DEFAULT   cast  strftime   s    now   as int                              dump text  ------------- Table  sqlite sequence CREATE TABLE sqlite sequence name seq  ------------- Table  Version CREATE TABLE Version                           id INTEGER PRIMARY KEY AUTOINCREMENT                          version text                          createdDate integer 4  DEFAULT   cast  strftime   s    now   as int                               updatedDate integer 4  DEFAULT   cast  strftime   s    now   as int                               sql text  ------------- Table  Tags CREATE TABLE Tags           id INTEGER PRIMARY KEY AUTOINCREMENT          name text          tag text          createdDate integer 4  DEFAULT   cast  strftime   s    now   as int               updatedDate integer 4  DEFAULT   cast  strftime   s    now   as int                -------------

User · Answer

In case you re having this problem in flex adobe air and find yourself here first  i ve found a solution  and have posted it on a related question  ADD COLUMN to sqlite db IF NOT EXISTS - flex air sqlite   My comment here  https   stackoverflow com a 24928437 2678219

User · Answer

I come up with this query SELECT CASE  SELECT count    FROM pragma table info   product    c WHERE c name     purchaseCopy    WHEN 0 THEN ALTER TABLE product ADD purchaseCopy BLOB END   Inner query will return 0 or 1 if column exists  Based on the result  alter the column

User · Answer

For those want to use pragma table info   s result as part of a larger SQL  select count    from pragma table info   lt table name gt    where name   lt column name gt      The key part is to use pragma table info   lt table name gt    instead of pragma table info   lt table name gt      This answer is inspired by  Robert Hawkey  s reply  The reason I post it as a new answer is I don t have enough reputation to post it as comment

User · Answer

Here is my solution  but in python  I tried and failed to find any post on the topic related to python       modify table for legacy version which did not have leave type and leave time columns of rings3 table  sql    PRAGMA table info rings3     get table info  returns an array of columns  result   inquire  sql    call homemade function to execute the inquiry if len result  lt   6    if there are not enough columns add the leave type and leave time columns     sql    ALTER table rings3 ADD COLUMN leave type varchar      commit sql    call homemade function to execute sql     sql    ALTER table rings3 ADD COLUMN leave time varchar      commit sql    I used PRAGMA to get the table information  It returns a multidimensional array full of information about columns - one array per column  I count the number of arrays to get the number of columns  If there are not enough columns  then I add the columns using the ALTER TABLE command

User · Answer

I have a 99  pure SQL method  The idea is to version your schema  You can do this in two ways    Use the  user version  pragma command  PRAGMA user version  to store an incremental number for your database schema version  Store your version number in your own defined table    In this way  when the software is started  it can check the database schema and  if needed  run your ALTER TABLE query  then increment the stored version  This is by far better than attempting various updates  blind   especially if your database grows and changes a few times over the years

User · Answer

If you are doing this in a DB upgrade statement  perhaps the simplest way is to just catch the exception thrown if you are attempting to add a field that may already exist   try      db execSQL  ALTER TABLE     TABLE NAME     ADD COLUMN foo TEXT default null      catch  SQLiteException ex       Log w TAG   Altering     TABLE NAME          ex getMessage

User · Answer

I solve it in 2 queries  This is my Unity3D script using System Data SQLite  IDbCommand command   dbConnection CreateCommand                command CommandText     quot SELECT count    FROM pragma table info  Candidat   c WHERE c name    BirthPlace  quot               IDataReader reader   command ExecuteReader                while  reader Read                                  try                                       if  int TryParse reader 0  ToString    out int result                                                 if  result    0                                                        command   dbConnection CreateCommand                                command CommandText     quot ALTER TABLE Candidat ADD COLUMN BirthPlace VARCHAR quot                               command ExecuteNonQuery                                command Dispose                                                                                      catch   throw

User · Answer

You can alternatively use the CASE-WHEN TSQL statement in combination with pragma table info to know if a column exists   select case CNT       WHEN 0 then printf  not found       WHEN 1 then printf  found       END FROM  SELECT COUNT    AS CNT FROM pragma table info  myTableName   WHERE name  columnToCheck

User · Answer

select   from sqlite master where type    table  and tbl name    TableName  and sql like   ColumnName    Logic  sql column in sqlite master contains table definition  so it certainly contains string with column name  As you are searching for a sub-string  it has its obvious limitations  So I would suggest to use even more restrictive sub-string in ColumnName  for example something like this  subject to testing as     character is not always there   select   from sqlite master where type    table  and tbl name    MyTable  and sql like    MyColumn  TEXT

[sqlite] ALTER TABLE ADD COLUMN IF NOT EXISTS in SQLite

Examples related to sqlite

Examples related to alter-table