How to rename a column in SQLite

We have a table in SQLite:

CREATE TABLE table_name (
	column1 INT,
	column2 INT,
	column3 INT);

Now you want to rename the column2 to column2_2. Unfortunately, SQLite does not directly support the ALTER TABLE RENAME COLUMN statement that allows you to rename an existing column of a table.

The workaround for that is to create a new table with the list of column names that you want, copy data from the old table to the new table, then drop the old table.

First, rename the table:

ALTER TABLE table_name RENAME TO temp_table_name;

Then create a new table with column2_2, the rest is kept the same:

CREATE TABLE table_name (
	column1 INT,
	column2_2 INT,
	column3 INT);

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

INSERT INTO table_name(column1, column2_2, column3)
SELECT column1, column2, column3
FROM temp_table_name;

Lastly, drop the old table:

DROP TABLE temp_table_name;

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

TablePlus GUI Tool SQLite