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?

score 71 · Answer 1 · edited Jan 26 '18 at 18:19

71

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.

edited Jan 26 '18 at 18:19

ArieKanarie

944
1
15
29

answered Aug 30 '10 at 21:35

MPelletier

16,256
15
86
137

7

What is the initial value of `user_version`? I assume zero, but it would be nice to see that documented. – Craig McQueen Mar 03 '14 at 02:57
1

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"? – Craig McQueen Mar 03 '14 at 03:06
@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". – MPelletier Mar 03 '14 at 04:07
1

@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. – MPelletier Mar 03 '14 at 04:09
7

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. – Craig McQueen Mar 03 '14 at 05:27
1

@CraigMcQueen I agree, but it doesn't appear to be documented. – MPelletier Mar 03 '14 at 13:30
can you provide an full exemple? – Reign.85 Oct 28 '14 at 11:59
@Reign.85 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 :) – MPelletier Oct 30 '14 at 19:41
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? – WinEunuuchs2Unix Feb 17 '21 at 12:32

score 37 · Answer 2 · answered Apr 20 '17 at 15:47

37

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

answered Apr 20 '17 at 15:47

user7896780

387
3
2

4

I do not like exception-style programming, but this is amazingly clean. Maybe you have swayed me a bit. – Stephen J Nov 01 '17 at 16:04
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". – tmighty Dec 18 '17 at 13:18
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. – maplemale Mar 10 '18 at 20:11
While others don't like it, I think this is the best solution lol – Adam Varhegyi Jun 11 '19 at 07:25
1

Simple, but how do we distinguish between an exception caused by prior existence of the column, and failure to add the column? – Andy Thomas Jan 05 '23 at 20:02
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. – Joshua Pinter Aug 29 '23 at 14:11

score 36 · Answer 3 · edited Mar 04 '21 at 17:14

36

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	dflt_value	pk
0	id	integer	null	1
1	type	text	null	0
2	data	json	null	0

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

edited Mar 04 '21 at 17:14

Beeno Tung

1,058
10
17

answered Sep 28 '12 at 12:11

Robert Hawkey

761
7
12

50

Your answer would be much more excellent were you to provide the code with which to complete that search instead of just a link. – Michael Alan Huff Apr 09 '14 at 23:31
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. – Hao Nguyen Jul 20 '14 at 19:39
2

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? – Michael Nov 03 '15 at 20:27
1

@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 – Kowlown Dec 02 '15 at 16:23
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 }) }` – Ammar Mujeeb Jun 13 '18 at 06:32
1

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 :). – Ben Farmer Dec 15 '18 at 10:01
1

@BenFarmer probably you'd want to do it in a transaction then. – Robert Hawkey Dec 17 '18 at 15:34

score 34 · Answer 4 · answered Apr 23 '15 at 09:36

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

Krunal Shah · Answer 5 · 2019-01-16T04:25:20.863

15

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

edited Jan 16 '19 at 04:25

answered Dec 01 '16 at 10:58

Krunal Shah

1,438
1
17
29

Cursor cursor = db.rawQuery("select * from tableName" , null); columns = cursor.getColumnNames(); – Vahe Gharibyan Sep 16 '17 at 14:38
1

I guess you forgot to close the cursor :-) – Pecana Jan 14 '19 at 15:57
@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` :)). – Farid Mar 02 '19 at 06:23
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. – PressingOnAlways Apr 04 '19 at 00:37

score 14 · Answer 6 · answered Jul 13 '20 at 08:49

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.

score 0 · Answer 7 · edited May 23 '17 at 12:18

0

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

edited May 23 '17 at 12:18

Community

1
1

answered Jul 24 '14 at 08:39

stevesweets

41
5

score 0 · Answer 8 · answered Jun 21 '19 at 00:25

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

score 0 · Answer 9 · answered Sep 25 '19 at 20:09

0

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

answered Sep 25 '19 at 20:09

kevinH

91
3

3

here how do we alter table? when there is column name match? – Kannan Sep 28 '20 at 14:00

score 0 · Answer 10 · answered Apr 30 '20 at 05:01

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.

score 0 · Answer 11 · answered Aug 16 '20 at 01:19

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

Jaro B · Answer 12 · 2020-11-03T10:07:41.367

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

Tyler Bowers · Answer 13 · 2023-08-13T04:10:23.440

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}")

score -1 · Answer 14 · answered Oct 25 '20 at 05:03

-1

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

answered Oct 25 '20 at 05:03

Aravin

6,605
5
42
58

6

code = Error (1), message = System.Data.SQLite.SQLiteException (0x800007BF): SQL logic error near "ALTER": syntax error at System.Data.SQLite.SQLite3.Prepare – インコグニトアレクセイ Nov 09 '20 at 08:17
7

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. – Neekobus Dec 01 '20 at 11:22
this is incorrect and not a valid answer – Michael Brown Mar 22 '23 at 02:23

score -1 · Answer 15 · answered Nov 09 '20 at 08:33

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

score -1 · Answer 16 · answered Jul 19 '21 at 18:52

-1

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.

answered Jul 19 '21 at 18:52

Richard

10,122
10
42
61

1

Not true; I get `duplicate column name: is_hidden` for repeated `ADD COLUMN is_hidden`. – Rudolf Adamkovič Apr 10 '23 at 14:29

ALTER TABLE ADD COLUMN IF NOT EXISTS in SQLite

16 Answers16

Linked

Related