sqlsqlitealter-table

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

I need to rename table columns in an SQLite database. This similar question doesn't mention SQLite. From the documentation of ALTER TABLE I gather that it's not a single statement.

What is a generic SQL way of doing this with SQLite?

Solution

  • 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.

    enter image description here 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.