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.
This question is related to
sqlite
alter-table
It is possible by recreating table.Its work for me please follow following step:
do all above steps in worker thread to reduce load on uithread
If you prefer a GUI, DB Browser for SQLite will do this with a few clicks.
DB Browser for SQLite is open source and free. For Linux it is available from the repository.
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
Source: Stackoverflow.com