67

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.

ROMANIA_engineer
  • 54,432
  • 29
  • 203
  • 199
Jorge Israel Peña
  • 36,800
  • 16
  • 93
  • 123
  • Perhaps helpful to others wondering how to avoid this in the first place... Specify the column characteristics after the column name. An example: `ALTER TABLE thetable ADD COLUMN thenewcolumn INTEGER DEFAULT 0`. – dat Oct 10 '18 at 17:42
  • SQLite has limited ALTER functionality now: https://www.sqlite.org/lang_altertable.html – CAD bloke Feb 14 '20 at 04:19

5 Answers5

57

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

See also:

Community
  • 1
  • 1
  • So if I didn't define a type, I should still be able to insert text into it? Of course later when I deploy to production I will explicitly state the type (text), but for now I should be fine? – Jorge Israel Peña Jan 18 '10 at 02:31
  • 2
    Yes. You can insert non-text values into that column too: "SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container." (from my second link) Specifying the types of columns has much less significance in SQLite than other RDBMs. –  Jan 18 '10 at 02:39
  • 2
    This is true... but if you later create other tables with the data type you would have used, then then your join queries wont be able to use indexes because the columns wont have the same type – DallinDyer Mar 12 '13 at 17:56
  • Not having a type on a column will also cause problems with where clauses. For example, if you declare an _id column without a type, a where clause that uses a placeholder (e.g., `where _id=?`) will never return any rows because the final query will use `where _id='1'` and SQLite won't know how to do the comparison properly. It will compare the two as a string (from the where clause) and an integer (from the row data) and it will be false every time. Omitting a column type seems like a big oversight but SQLite allowing it seems like an even bigger oversight. – spaaarky21 Jul 07 '14 at 16:55
  • 4
    So if you define a column type as `INT` then you can insert `VARCHAR` values into it? – Pierre Apr 03 '15 at 18:38
30

If you prefer a GUI, DB Browser for SQLite will do this with a few clicks.

  1. "File" - "Open Database"
  2. 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.

user4157124
  • 2,809
  • 13
  • 27
  • 42
Insilico
  • 866
  • 9
  • 10
  • 1
    What do you mean by "*click on the table content (not table name)*"? Under the "Database Structure" tab, I did have to click on the table name under Tables --> Mytable . I was also able to right-click on the table name to bring up a popup menu, where you can choose "Modify Table". – stackoverflowuser2010 Jul 03 '17 at 16:25
  • 1
    back up your database before attempting any DB Browser table modifications on an existing sqlite database: https://github.com/sqlitebrowser/sqlitebrowser/issues/1481 – Demian Sep 20 '18 at 17:02
  • This option does not seem to exist in that menu in the more recent versions ... maybe it was dropped – matanster Mar 12 '21 at 19:34
  • 1
    To clarify the original answer and several questions. As of 5/2021 the modify table option is still there. If you click on the "Schema" column for the table in question, then go to the edit menu you'll find a modify table option. However, be advised that this doesn't work correctly if you have views defined on the table in question as the tool does this by copying and recreating the table. – james May 21 '21 at 19:04
  • Works like a charm. To call from CLI use `sqlitebrowser /path/to/databsae.db` – WinEunuuchs2Unix May 05 '23 at 16:02
9

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

surfmuggle
  • 5,527
  • 7
  • 48
  • 77
Byzantius
  • 91
  • 1
  • 4
6

It is possible by recreating table.Its work for me please follow following step:

  1. create temporary table using as select * from your table
  2. drop your table, create your table using modify column type
  3. now insert records from temp table to your newly created table
  4. drop temporary table

do all above steps in worker thread to reduce load on uithread

JJD
  • 50,076
  • 60
  • 203
  • 339
RD-07
  • 71
  • 1
  • 1
  • 1
    I'm wondering, if you could add the code to your answer, please? – Gооd_Mаn Sep 30 '19 at 16:43
  • 3
    this is an example: ```CREATE TABLE temp AS select a, b, c, CAST(d AS INTEGER) AS d_new from original;``` Delete later what you don't need – Kay Mar 25 '20 at 16:14
0

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" >$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
rubo77
  • 19,527
  • 31
  • 134
  • 226