SQLITE syntax error code 1 when renaming a column name

Question

I am migrating a Room database in my Android app. This is the migration code:

static final Migration MIGRATION_1_2 = new Migration(1, 2) {
    @Override
    public void migrate(SupportSQLiteDatabase database) {
        database.execSQL("ALTER TABLE item RENAME itemInfoId TO itemId");
    }
};

The error message

android.database.sqlite.SQLiteException: near "itemInfoId": syntax error (code 1 SQLITE_ERROR): , while compiling: ALTER TABLE item RENAME itemInfoId TO itemId

I have also tried the SQL of "ALTER TABLE item RENAME COLUMN itemInfoId TO itemId", same error

android.database.sqlite.SQLiteException: near "COLUMN": syntax error (code 1 SQLITE_ERROR): , while compiling: ALTER TABLE item RENAME COLUMN itemInfoId TO itemId

Cairn

Answer 1

Rename keyword is available in SQLite version 3.25 which is not available for the latest android version. You will have to manually upgrade the table

1. Create item_tmp table with correct column value itemId

CREATE TABLE item_tmp(<column1> <data_type>, itemId <data_type>,.....)

2. Copy the data from item to item_tmp

INSERT INTO item_tmp(<column1>, <column2>,..) 
    SELECT <column1>, <column1>, ... 
    FROM item;

3. Drop table item

DROP TABLE item;

4. Rename the item_tmp table

ALTER TABLE item_tmp RENAME TO item;

Ibrahim

I saw the same question from 2018 I think. I would have thought that this would be fixed by now. Any tips on manually upgrading the table? I already had to do it once with 20 records, it wasn't a big problem but doing it again would be quite tedious and in the future there will be hundreds of records. – Cairn

Updated the answer. – Ibrahim

Note that the order of operations in this answer is one explicitly called out as incorrect in the documentation. – Apeman

Thanks, @Apeman and user2864740. I have updated the answer please verify. – Ibrahim

Is there perhaps a generic way to do it? Some like what I see on removal of columns, as here: https://mcmap.net/q/93030/-how-to-delete-or-add-column-in-sqlite – Wildawildcat

I am getting this issue with SQLite version 3.32.2 2020-06-04 12:58:43 – Shaunna

The second step is incorrect. It should be INSERT INTO item_tmp SELECT FROM item. – Compressed

Answer 2

i have faced same problem while using RENAME keyword in Sqlite. it gives error in below android 10 device and working perfectly in android 11 and above.

Pinky

Thank you for pointing out this issue, I am facing the same problem. Do you have a solution for that? – Timberland

You have to write the complete migration script for the room database. Renaming the field needs a lot of steps. – Pinky

I'll share a migration script if you need it – Pinky

I am using SQLite, and this same problem exists even on Android 13. I want to know if this problem is fixed or not. – Timberland