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

This was just fixed with 2018-09-15 (3.25.0)

Enhancements the ALTER TABLE command:

• Add support for renaming columns within a table using ALTER TABLE table RENAME COLUMN oldname TO newname.
• Fix table rename feature so that it also updates references to the renamed table in triggers and views.

You can find the new syntax documented under ALTER TABLE

The RENAME COLUMN TO syntax changes the column-name of table table-name into new-column-name. The column name is changed both within the table definition itself and also within all indexes, triggers, and views that reference the column. If the column name change would result in a semantic ambiguity in a trigger or view, then the RENAME COLUMN fails with an error and no changes are applied.

^{Image source: https://www.sqlite.org/images/syntax/alter-table-stmt.gif}

Example:

CREATE TABLE tab AS SELECT 1 AS c;

SELECT * FROM tab;

ALTER TABLE tab RENAME COLUMN c to c_new;

SELECT * FROM tab;

db-fiddle.com demo

---

Android Support

As of writing, Android's API 27 is using SQLite package version 3.19.

Based on the current version that Android is using and that this update is coming in version 3.25.0 of SQLite, I would say you have bit of a wait (approximately API 33) before support for this is added to Android.

And, even then, if you need to support any versions older than the API 33, you will not be able to use this.

Digging around, I found this multiplatform (Linux | Mac | Windows) graphical tool called DB Browser for SQLite that actually allows one to rename columns in a very user friendly way!

Edit | Modify Table | Select Table | Edit Field. Click click! Voila!

However, if someone want to share a programmatic way of doing this, I'd be happy to know!

Note that as of version 3.25.0 released September 2018 you can now use ALTER TABLE to rename a column.

Original "create new and drop old table" answer below.

---

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

First create the new table with a temporary name, based on the old table definition but with the updated column name:

CREATE TABLE tmp_table_name (
  col_a INT
, col_b INT
);

Then copy the contents across from the original table.

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

Drop the old table.

DROP TABLE orig_table_name;

Last you rename the temporary table table to the original:

ALTER TABLE tmp_table_name RENAME TO orig_table_name;

Don't forget to re-create indexes, triggers, etc. The documentation gives a fuller picture of the gotchas and caveats.

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