Modify a Column's Type in sqlite3

Question

I'm pretty new to SQLite 3 and just now I had to add a column to an existing table I had. I went about doing that by doing: ALTER TABLE thetable ADD COLUMN category;.

Of course, I forgot to specify that column's type. The first thing I was thinking about doing was dropping that column and then re-adding it. However, it seems that SQLite does not have a simple way of doing this, and I would have had to backup the table and re-create it without the column.

This seems messy, and I was wondering if there were just a way of modifying/adding a column's type. I would imagine so, but my searching around yielded no results, being new to SQLite, I imagine it was due to my wording being off in the query.

Admin · Answer

SQLite doesn't support removing or modifying columns, apparently. But do remember that column data types aren't rigid in SQLite, either.

See also:

SQLite Modify Column

user_17450555 · Answer

There is a much simpler way:

ALTER TABLE your_main_table ADD COLUMN new_column_name new_column_data_type
UPDATE your_main_table SET new_column_name = CAST(old_column_name as new_data_type_you_want)

I tried this on my machine locally and it works

Modify a Column's Type in sqlite3

5 Answers