Need a good method to change (SQLite) column data type

Question

I am having a table with 'int' column. During software upgrade, I want to change it to 'long' data type. It seems SQLite does not provide an option (in alter statement) to change/modify column properties. Since I want to do an upgrade, the table might contain data which the user should not lose. So please suggest me a good method to change the column data type property without data loss.

One way suggested in the search links is to create a temporary table, copy the records from the existing table, delete the existing table, and rename the temporary table. I doubt that is efficient.

Your help appreciated!

Regards

Vivek Ragunathan

Answer 1

I used the follow statements to change the type of the column.

CREATE TABLE IF NOT EXISTS **TEMP_TABLE** (id integer primary key autoincrement, **col2change integer not null**, ...)

INSERT INTO TEMP_TABLE SELECT * FROM EXISTING_TABLE

DROP TABLE EXISTING_TABLE

ALTER TABLE TEMP_TABLE RENAME TO EXISTING_TABLE

I changed the int type column in the existing table to integer type. For a few hundred rows, it was reasonably fast.

Answer 2

SQLite3 columns do not have data types, only affinities -- there is no benefit in changing the column type from int to long.

If a wrapper program is truncating values before giving them to SQLite3, there is a way to fix this by editing the schema. It is a dangerous operation, so do it only after backing up your database. The schema is stored in the table sqlite_master; normally it is read-only, but you can modify it if you enable it with the writable_schema pragma. Be careful to only make changes that do not invalidate your data; you may change int to long int since they both have INTEGER affinity.