How to use Alter table Alter column query in SQLite on android?

Question

Possible Duplicate:
ALTER COLUMN in sqlite

I want to change the data type of a column while upgrading. Any idea how to change the data type of a column.

While using Alter table Alter column statement it is giving a syntax error?

Answer 1

You can't, SQLite's ALTER TABLE is quite limited:

so you can rename a table with ALTER TABLE or you can add a single new column. On the upside (at least for you), SQLite's notion of "type" is pretty loose and depends more on how you use something than how you've defined the column; for example:

sqlite> create table t (i int);
sqlite> insert into t (i) values (11);
sqlite> insert into t (i) values ('pancakes');
sqlite> select * from t;
i
11
pancakes

so you can put data of any type in a column of any other type.

However, if you're using a tool that checks the table's schema in order to determine how to convert the table's data into native types, then you'll need to simulate an ALTER TABLE ALTER COLUMN. AFAIK, the only way to do that is the ugly way:

1. Create a new table that has the schema you're looking for.
2. Copy everything from your old table to the new table.
3. Drop the original table.
4. Use ALTER TABLE RENAME to rename your new correct table to the original name.

I'd do it the ugly way and ignore SQLite's lax type system; if I find 'pancakes' in a column that is declared as an integer, I'd be a bit puzzled and have some harsh words for the responsible person.

Answer 2

As it is stated in this page

http://www.sqlite.org/lang_altertable.html

Sqlite does not support the ALTER TABLE ALTER COLUMN syntax.

As I see it you have to store the values of your columns somewhere else, add a new column, rewrite the stored values into the new column, and then drop your old column.