How to add a new column to Sqlite database table which does not exist in android?

Question

On Version upgrade i want to add a new column to the the sqlite database table which is not exsit in android. if the column is already exists it should not alter the table. In onUpgrade() method i am not droping the table becoz i dont want to lose the data.

score 4 · Answer 1 · answered Jul 18 '14 at 09:51

I pieced a few comments together to get this:

Cursor cursor = database.rawQuery("SELECT * FROM MY_TABLE", null); // grab cursor for all data
int deleteStateColumnIndex = cursor.getColumnIndex("MISSING_COLUMN");  // see if the column is there
if (deleteStateColumnIndex < 0) { 
    // missing_column not there - add it
    database.execSQL("ALTER TABLE MY_TABLE ADD COLUMN MISSING_COLUMN int null;");
}

This intentionally ignores database version number and purely adds the column if it isn't there already (in my case, the version numbers didn't help me as the numbering had gone wonky when this column was supposed to have been added)

What if there is no row on your table ? – TheSquad Nov 13 '14 at 17:50 — TheSquad, Nov 13 '14 at 17:50

Sunny · Answer 2 · 2013-10-10T06:23:49.897

3

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    // If you need to add a column
    if (newVersion > oldVersion) {

     if(!ColunmExists) {
        db.execSQL("ALTER TABLE foo ADD COLUMN new_column INTEGER DEFAULT 0");
     }
    }
}

edited Oct 10 '13 at 06:23

answered Oct 10 '13 at 05:06

Sunny

14,522
15
84
129

1

how can we check in onCreate() or onUpgrade() itself weather the column exists or not ??? what is ColunmExists??? – Jagadeesh Oct 10 '13 at 06:00
2

Simple make a query on your table `cursor.getColumnIndex(String columnName) returns -1` if column does not exists. – Sunny Oct 10 '13 at 06:03
`ColunmExists` is just a variable. – Sunny Oct 10 '13 at 06:05
do you think initiating the cursor and calling and using it for every version upgrade in onCreate() or onUpgrade() is a good programming practice.. Any better solution is appreciable. – Jagadeesh Oct 10 '13 at 06:16
This will not hurt your program. This is normal for an application. You just need to initialize Cursor if `newVersion>oldVersion`. So it will not be called every time. See the edits. – Sunny Oct 10 '13 at 06:23
if we can check the existence of the new column with in the same query of altering the table it will be good. Is it possible to check like that? – Jagadeesh Oct 10 '13 at 06:28
FYI, no need to check the condition if(newVersion > oldVersion) in onUpgrade() method because this method will be called only on increment of the database version number. – Jagadeesh Oct 10 '13 at 06:36
Just run the query no need to check if column exist or not. It will throw a Sql exception if column already exists. So just surround it with a try catch block. – Sunny Oct 10 '13 at 06:41
@Sunny I think this answer as is makes the issue much more confusing. – Eliezer Apr 10 '14 at 21:20

Sayem · Answer 3 · 2021-10-05T07:08:05.133

I used pragma to find out column existed or not.

fun SupportSQLiteDatabase.safeRunQueryToAddColumn(
tableName: String,
columnName: String,
block: () -> Any) {
    
    val cursor = query(
        "SELECT count(*) FROM pragma_table_info('$tableName') WHERE name='$columnName'", null)

    val columnExisted = if (cursor.moveToNext()) {
        cursor.getInt(0) == 1 // row found
    } else false

    cursor.close()


    if (!columnExisted) {
        block()
    } else {
        Log.w(
            "ERROR",
            "column add ignored, table : $tableName : column $columnName already existed"
        )
    }
}

Ref link

How to add a new column to Sqlite database table which does not exist in android?

3 Answers3