SQLite - INSERT a column in between two existing columns

INSERT a column in between two existing columns in SQLite

You have a table class:

CREATE TABLE class (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
score  INTEGER);

If you want to add a new column subject:

ALTER TABLE class ADD COLUMN subject INTEGER;

In SQLite, rearranging the column position in a table is not permitted. But there’s workaround for that which is to create a new table with the column position that you want, copy the data from the old table to the new table, and drop the old table.

If you want to add a column subject in between name and score

First, rename the table:

ALTER TABLE class RENAME TO temp_class;

Then create a new table with subject, keep the rest the same

CREATE TABLE class (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
subject INTEGER,
score  INTEGER);

Then copy the content from original table to the new table.

INSERT INTO class(id, name, score)
SELECT id, name, score
FROM temp_class;

Lastly, drop the old table:

DROP TABLE temp_class;

---

Need a good GUI tool for SQLite? Check out TablePlus. It’s native, beautiful, and available for free.

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

On Linux? Download TablePlus for Linux

Need a quick edit on the go? Download TablePlus for iOS