Change column type in sqlite database table

sqlite database is a very nice and powerful database and can easily embedded in your application. It have nice facility for create , insert,update and delete statement. It also supports various data types like int, real for float, text for string and etc. In android application you can find good wrappers for sqlite database.

I have also used sqlite database in estimation app which is available on google play store. This app gives facility to prepare estimates for the products/services they are selling. There is one screen in app where user can add item to estimation. Here in quantity field in earlier version we were using integer.

Estimation app add item screen
Estimation app add item screen

But recently one of our customer request to change quantity field to accept float values. I have search in google that how can i change table schema to accept float value which is integer. But after searching i found that sqlite database does not support alter statement at column level. But i found that there is a work around to update column type.

Solution:

  1. Rename original table with different name.

  2. Create new table with same name as previous table have with desired column type.

  3. Copy all previous data from old table (renamed table) to newly created table.

  4. Drop renamed table.

Example table with all sql statements in steps, show table with three or four column only.

Let say we have one table called items which have below schema.

id(int),name(text),stock_quantity(int), price(real)

Now suppose you have to alter stock_quantity column to float using above steps you can achieve it by following the steps mentioned below:

  • Rename items table to items_old:
ALTER TABLE items RENAME TO items_old
  • Create new table with same name as previous table have with desired column type.
CREATE TABLE items (

id integer PRIMARY KEY,

name text NOT NULL,

stock_quantity real NOT NULL

);
  • Copy all previous data from old table (renamed table) to newly created table.
INSERT INTO items (id,name,stock_quantity) SELECT id,name,stock_quantity FROM items_old;
  • Drop renamed table.
DROP TABLE items_old;
Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s