Android SQLite update column data type

Question

How can I change the data type of one column to another?

I want to change the data type of one column form Number to Text of existing table on database upgrade.

Answer 1

As far as I know you cannot change it, so easily with an alter table or something like that because there are some restrictions in SQLite:

Complete ALTER TABLE support

Only the RENAME TABLE and ADD COLUMN variants of the ALTER TABLE command are supported. Other kinds of ALTER TABLE operations such as DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT, and so forth are omitted.

Omitted information in SQLite

However, I have some advice based on my previous experience where I have needed to recreate tables:

1. To create a BackUp of the current information (storing the information in a XML or Cursor, it's just temporal). More information about cursors: Accessing Data With Android Cursors

2. To drop the table that you want to change.

   db.execSQL("DROP TABLE IF EXISTS YourTableName");

3. To recreate again the table with the new structure.

   db.execSQL("CREATE TABLE YourTableName");

4. To insert again the old information with a loop.

WATCH OUT: remember that you need to cast the column that before was a Number and now is a Text.