Is SQLite support Alter with Modify command or not

Question

I am using Sqlite as a database in my project. I am trying to using Alter with modify command but i fail every time.

switch (oldVersion) {
    case 1:
        db.execSQL("ALTER TABLE " + TABLE_NAME + " MODIFY "
                + MessageCountConstant.DATE + " INTEGER");
    default:
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(db);
    }

error logs which i got :

01-10 14:51:56.242: E/Database(1279): Failure 1 (near "MODIFY": syntax error) on 0x3702c0 when preparing 'ALTER TABLE messageCount MODIFY date INTEGER'.

can anyone tell me about the reason of this and also proper answer . Thanks in advance.

@dd619 . no i not just want to change name but i want to change the datatype of column. — nitin tyagi, Jan 10 '13 at 10:29

dd619 · Answer 1 · 2013-01-10T10:51:22.000

3

To rename your table name use,

alter table current_name rename to new_name;

changing the column datatype is not possible directly,but you can use the following trick,

create table if not exists new_table (id integer, name text )

insert into new_table select * from old_table

drop table old_table

alter table new_table rename to old_table

the above code create the new table with whatever the datatype you want then copy records from the old table,then delete the old table and finally,rename the new table to the old one.

edited Jan 10 '13 at 10:51

answered Jan 10 '13 at 10:38

dd619

5,910
8
35
60

i dont want to rename my table but i want to change the data type of column. – nitin tyagi Jan 10 '13 at 10:42

score 2 · Answer 2 · edited May 23 '17 at 12:16

2

As can be seen here http://www.sqlite.org/lang_altertable.html SQLite doesn't support modifying a column type. You can drop the table and recreate it or take a dump the database and change the column type manually. (here)

edited May 23 '17 at 12:16

Community

1
1

answered Jan 10 '13 at 10:20

K Roobroeck

1,378
2
10
13

ok Thanks. I got it..@ user1767746 can anyone tell me one more thing In my Sqlite DataBase I saved date in a data type DATE . how can i fetch this date from cursor. Thanks in advance. – nitin tyagi Jan 10 '13 at 10:25
@nitintyagi http://stackoverflow.com/questions/9303115/convert-date-from-sqlite-and-populate-a-listview – K Roobroeck Jan 10 '13 at 10:37

Is SQLite support Alter with Modify command or not

2 Answers2