SQLite - How to rename a column?
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.
Not on Mac? Download TablePlus for Windows.
On Linux? Download TablePlus for Linux
Need a quick edit on the go? Download TablePlus for iOS