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?

Rahel

Answer 1

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.

Holliholliday

What is the initial value of user_version? I assume zero, but it would be nice to see that documented. – Hiragana

Even with this, can it be done in pure SQL, since sqlite doesn't support IF and the ALTER TABLE doesn't have a conditional? What do you mean by "99% pure SQL"? – Hiragana

@CraigMcQueen No, it cannot be done in pure SQL with SQLite. By "99% pure", I meant you have to manage your schema versioning by hand in another language. It's a better design than "throw the alter table at it and hope it works". – Holliholliday

@CraigMcQueen As for the initial value of user_version, it appears to be 0, but it's really a user-defined value, so you can make your own initial value. – Holliholliday

The question about user_version initial value is relevant when you've got an existing database, and you've never used the user_version before, but you want to start using it, so you need to assume sqlite set it to a particular initial value. – Hiragana

@CraigMcQueen I agree, but it doesn't appear to be documented. – Holliholliday

can you provide an full exemple? – Uncrown

@Uncrown If I do I'll make it off-site, because this question is about SQLite and not whatever language SQLite is called with. I'll signal you when (and if) I do it. But I plan to :) – Holliholliday

Is there an example of how to "get" and "set" the version number? Then perhaps how to insert two new columns between two existing columns? – Manson

Answer 2

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());
}

Revulsion

I do not like exception-style programming, but this is amazingly clean. Maybe you have swayed me a bit. – Soapbark

I don't like it either, but C++ is the most exception style programming language ever. So I guess one may still see it as "valid". – Physicist

My use case for SQLite = I don't want to do a ton of extra coding for something stupid simple/one liner in other languages (MSSQL). Good answer... though it is "exception style programming" it's in an upgrade function / isolated so I suppose it's acceptable. – Chart

While others don't like it, I think this is the best solution lol – Godard

Simple, but how do we distinguish between an exception caused by prior existence of the column, and failure to add the column? – Biles

There may be a number of different reasons that you get a SQLiteException. If you want to be very explicit, which I recommend being when handling exceptions, you could also check that it starts with "duplicate column name", like if ( ex.getMessage().startsWith( "duplicate column name" ) ). If it does, handle it gracefully. If it doesn't, you might want to re-throw the exception with throw ex. Just a thought to ensure you're not muffling other exceptions. – Kathyrnkati

Answer 3

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)

Sample output:

cid	name	type	notnull	dflt_value	pk
0	id	integer	0	null	1
1	type	text	0	null	0
2	data	json	0	null	0

http://www.sqlite.org/pragma.html#pragma_table_info

Zoellick

Your answer would be much more excellent were you to provide the code with which to complete that search instead of just a link. – Fastening

PRAGMA table_info(table_name). This command will list each column of the table_name as a row in the result. Based on this result, you can determine if the column existed or not. – Zoometry

Is there some way to do this by combining the pragma in part of a larger SQL statement such that the column is added if it doesn't exist but otherwise isn't, in only a single query? – Jinja

@Michael. As far as I know, no you can't. The problem with PRAGMA command is that you can't query on it. the command does not present data to the SQL engine, it returns results directly – Ashly

To give a basic idea i m copying my Swift code for checking if column already exists: public func exists(column: String, in table: String) throws -> Bool { let stmt = try prepare("PRAGMA table_info(\(table))") let columnNames = stmt.makeIterator().map { (row) -> String in return row[1] as? String ?? "" } return columnNames.contains(where: { dbColumn -> Bool in return dbColumn.caseInsensitiveCompare(column) == ComparisonResult.orderedSame }) } – Thierry

Doesn't this create a race condition? Say I check the column names, see that my column is missing, but in the meantime another process adds the column. Then I will attempt to add the column but will get an error because it already exists. I guess I am supposed to lock the database first or something? I am a noob to sqlite I am afraid :). – Braze

@BenFarmer probably you'd want to do it in a transaction then. – Zoellick

Just a very simple query. – Phoney

Answer 4

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<string, string> columnNameToAddColumnSql = new Dictionary<string, string>
{
    {
        "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));
    }
}

Uproarious

Answer 5

For those want to use pragma table_info()'s result as part of a larger SQL.

select count(*) from
pragma_table_info('<table_name>')
where name='<column_name>';

The key part is to use pragma_table_info('<table_name>') instead of pragma table_info('<table_name>').

---

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.

Pd

Answer 6

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 && !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);

Adieu

Cursor cursor = db.rawQuery("select * from tableName" , null); columns = cursor.getColumnNames(); – Disseise

I guess you forgot to close the cursor :-) – Dochandorrach

@VaheGharibyan, so you'll simply select everything in your DB just to get column names?! What you're simply saying is we give no shit about performance :)). – Perpetual

Note, the last query is incorrect. The proper query is: SELECT * FROM pragma_table_info(...) (note the SELECT and underscore between pragma and table info). Not sure what version they actually added it in, it didn't work on 3.16.0 but it works on 3.22.0. – Silva

Answer 7

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://mcmap.net/q/182964/-add-column-to-sqlite-db-if-not-exists-flex-air-sqlite

Caponize

Answer 8

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') 

Oar

here how do we alter table? when there is column name match? – Namnama

Answer 9

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%'

Vaios

Answer 10

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 &table, QString &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;
}

Hurricane

Answer 11

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)<= 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.

Gallic

Answer 12

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  "$0" ${1+"$@"}
foreach pkg {sqlite3 } {
    if { [ catch {package require {*}$pkg } err ] != 0 } {
    puts stderr "Unable to find package $pkg\n$err\n ... adjust your auto_path!";
    }
}
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 && [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 "Error:  $err ... trying again" 
            set cmd [removeAlterTable $cmd $columnname ]
        } else {
            throw DBERROR "$err\n$backtrace"
        }
        }
    }
    }
}
# return sqltext with alter table command with column name removed
# if no matching alter table line found or result is no lines then
# returns ""
proc removeAlterTable { sqltext columnname } {
    set mode skip
    set result [list]
    foreach line [split $sqltext \n ] {
    if { [string first "alter table" [string tolower [string trim $line] ] ] >= 0 } {
        if { [string first $columnname $line ] } {
        set mode add
        continue;
        }
    }
    if { $mode eq "add" } {
        lappend result $line
    }
    }
    if { $mode eq "skip" } {
    puts stderr "Unable to find matching alter table line"
    return ""
    } elseif { [llength $result ] }  { 
    return [ join $result \n ]
    } else {
    return ""
    }
}
               
proc printSchema { } {
    db eval { select * from sqlite_master } x {
    puts "Table: $x(tbl_name)"
    puts "$x(sql)"
    puts "-------------"
    }
}
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 ) ) 
        )
-------------

Fayefayette

not an answer to the question – Pella

Answer 13

Simple python code to add "your_column" to "your_table" if "your_column" is not already in the columns in the table. You can also just break if you find "your_column" in the for loop, for my particular case with this issue I cannot do this. You also might want to enforce case-sensitivity for comparisons by using .upper() or .lower() on col[1] and "your_column". If you are adding more than one column then I reccomend changing the for loop to delete the columns out of the list that you are adding.

cols = []
for col in DB.cursor.execute(f"PRAGMA table_info({your_table})").fetchall():
    cols.append(col[1])
if "your_column" not in cols:
      DB.cursor.execute(f"ALTER TABLE {your_table} ADD COLUMN {your_column} {your_datatype}")

K2

Answer 14

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

Santossantosdumont

code = Error (1), message = System.Data.SQLite.SQLiteException (0x800007BF): SQL logic error near "ALTER": syntax error at System.Data.SQLite.SQLite3.Prepare – Treasurer

You have a typo error with the 2 simple quotes around the strings (product and purchaseCopy) but I can't make it work because of the " THEN ALTER TABLE". Are you sure it's possible ? If this works, it should be the accepted answer. – Goldenrod

this is incorrect and not a valid answer – Barthelemy

Answer 15

I solve it in 2 queries. This is my Unity3D script using System.Data.SQLite.

IDbCommand command = dbConnection.CreateCommand();
            command.CommandText = @"SELECT count(*) FROM pragma_table_info('Candidat') c WHERE c.name = 'BirthPlace'";
            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 = @"ALTER TABLE Candidat ADD COLUMN BirthPlace VARCHAR";
                            command.ExecuteNonQuery();
                            command.Dispose();
                        }
                    }
                }
                catch { throw; }
            }

Treasurer

Answer 16

Apparently... in SQLite... the "alter table" statement does not generate exceptions if the column already exists.

Found this post in the support forumn and tested it.

Participle

Not true; I get duplicate column name: is_hidden for repeated ADD COLUMN is_hidden. – Booher