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!
Thijs Lensselink is a PHP developer, consultant and all out open source enthusiast.
He has over 12+ years of experience in building and maintaining web applications mostly
on linux/Unix/BSD platforms. Besides a full time job he does freelance work with his ...
Thijs,
Why didn’t you use the ALTER TABLE statement to change the datatype of a field?
There are 2 better ways to accieve it.
1. ALTER TABLE SYNC MODIFY DATA TEXT
- Changes the table data structure from varchar to text
Another way is to create a temporary column (for example when you want to change a field from varchar to int) update that field with the value of the field you want to change, drop or rename the old column and rename the new column to the old column.
1. ALTER TABLE SYNC ADD NUMNEW INT(10)
2. UPDATE SYNC SET NUMNEW = NUM
3. ALTER TABLE SYNC DROP NUM
4. ALTER TABLE SYNC RENAME NUMNEW NUM
Alex van der Vliet
12 Aug 09 at 07:17
Hey Alex,
I deleted my previous comment. Seems i was to sleepy in the morning to parse the SQL statement. Just gave it a shot and this actually seems to work. So thanks for the tip.
I have to admit that using a temp column over a temp table is preferable :)
Thijs Lensselink
12 Aug 09 at 07:22
Off course you can also use the CAST() and CONVERT() methods from MySQL to convert values from a old field to a new field.
For example if you have a table containing 2 text fields and 1 ID field but one text field should be an int field, just create a new table containing the structure from the old table and insert the rows into the new table with the command INSERT INTO TABLE2 (ID, NAME, VALUE) SELECT ID, NAME CAST(VALUE AS INTEGER) FROM TABLE1.
A quick way to get the create statement from an existing table is via the SHOW CREATE TABLE statement. So SHOW CREATE TABLE TABLE1 returns the complete create statement including keys from table TABLE1. Simply grab the response, change the tablename and fields and execute the statement to create your new table.
Alex van der Vliet
17 Aug 09 at 08:06