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

User · Answer

It is possible by dumping  editing and reimporting the table   This script will do it for you  Adapt the values at the start of the script to your needs       bin bash  DB  tmp synapse homeserver db TABLE  public room list stream  FIELD visibility OLD  BOOLEAN NOT NULL  NEW  INTEGER NOT NULL  TMP  tmp sqlite  TABLE sql  echo      create dump  echo   dump   TABLE     sqlite3   DB   gt  TMP  echo      editing the create statement  sed -i  s  FIELD  OLD  FIELD  NEW g   TMP  read -rsp   Press any key to continue deleting and recreating the table  TABLE     n  -n1 key   echo      rename the original to   TABLE  backup    sqlite3   DB   PRAGMA busy timeout 20000  ALTER TABLE   TABLE  RENAME TO   TABLE  backup     echo      delete the old indexes  for idx in   echo  SELECT name FROM sqlite master WHERE type     index  AND tbl name LIKE   TABLE         sqlite3  DB   do   echo  DROP INDEX   idx      sqlite3  DB done  echo      reinserting the edited table  cat  TMP   sqlite3  DB

User · Answer

It is possible by recreating table Its work for me please follow following step    create temporary table using as select   from your table drop your table  create your table using modify column type now insert records from temp table to your newly created table drop temporary table   do all above steps in worker thread to reduce load on uithread

User · Answer

If you prefer a GUI  DB Browser for SQLite will do this with a few clicks     File  -  Open Database  In the  Database Structure  tab  click on the table content  not table name   then  Edit  menu   Modify table   and now you can change the data type of any column with a drop down menu  I changed a  text  field to  numeric  in order to retrieve data in a number range    DB Browser for SQLite is open source and free  For Linux it is available from the repository

User · 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

[sqlite] Modify a Column's Type in sqlite3

Examples related to sqlite

Examples related to alter-table