Change Columns Data Type in Sqlite

My coworker Chris yesterday asked me how to change a column data type in a database. He wanted to change the data type from varchar to text. So my first reaction was to shout ALTER TABLE. But i didn’t realize he was using sqlite at the moment. So after reading the docs for a while. We came to the conclusion sqlite does support the ALTER statement. But it is very limited compared to say MySQL. And it doesn’t provide the option to alter the data types or column names.

So how should you change a column data type in an sqlite database? Well as far as i can see there is not really a simple solution. But here goes for our work around.

Let’s start off by creating a test database:

sqlite3 test.db “create table sync (id INTEGER PRIMARY KEY, data VARCHAR, num double, dateIn DATE);”

And insert some test data:

  • sqlite3 test.db “insert into sync (data, num) values (‘This is sampledata’, 3);”
  • sqlite3 test.db “insert into sync (data, num) values (‘More sample data’, 6);”
  • sqlite3 test.db “insert into sync (data, num) values (‘And a little more’, 9);”

Now comes the trick. We first create a temporary table and populate it with the data from the table we are going to alter.

sqlite3 test.db “create table temp_table as select * from sync;”

Drop the database to alter. And after that recreate it again. But this time with the changes we wanted to make. So we change the VARCHAR field to TEXT.

  • sqlite3 test.db “drop table sync;”
  • sqlite3 test.db “create table sync (id INTEGER PRIMARY KEY, data TEXT, num double, dateIn DATE);”

The only thing left now is to populate the new database with the data from the temporary table. And finally drop the temporary table.

  • sqlite3 test.db “insert into sync select * from temp_table;”
  • sqlite3 test.db “drop table temp_table;”

There must be a cleaner way to do this. But this worked for us!

comments powered by Disqus