How do I rename a column in a SQLite database table?

sqlite alter-table
joce picture joce · Apr 30, 2009 · Viewed 184.3k times · Source

I would need to rename a few columns in some tables in a SQLite database. I know that a similar question has been asked on stackoverflow previously, but it was for SQL in general, and the case of SQLite was not mentioned.

From the SQLite documentation for ALTER TABLE, I gather that it's not possible to do such a thing "easily" (i.e. a single ALTER TABLE statement).

I was wondering someone knew of a generic SQL way of doing such a thing with SQLite.

Answer

Evan picture Evan · Apr 30, 2009

Say you have a table and need to rename "colb" to "col_b":

First you rename the old table:

ALTER TABLE orig_table_name RENAME TO tmp_table_name;

Then create the new table, based on the old table but with the updated column name:

CREATE TABLE orig_table_name (
  col_a INT
, col_b INT
);

Then copy the contents across from the original table.

INSERT INTO orig_table_name(col_a, col_b)
SELECT col_a, colb
FROM tmp_table_name;

Lastly, drop the old table.

DROP TABLE tmp_table_name;

Wrapping all this in a BEGIN TRANSACTION; and COMMIT; is also probably a good idea.

Related questions

ALTER TABLE ADD COLUMN IF NOT EXISTS in SQLite

We've recently had the need to add columns to a few of our existing SQLite database tables. This can be done with ALTER TABLE ADD COLUMN. Of course, if the table has already been altered, we want to leave it …

Read More
Modify a Column's Type in sqlite3

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 …

Read More
SQLite Modify Column

I need to modify a column in a SQLite database but I have to do it programatically due to the database already being in production. From my research I have found that in order to do this I must do …

Read More