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

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.

Solution 1:

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.

Solution 2:

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.

Solution 3:

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!

Solution 4:

While it is true that there is no ALTER COLUMN, if you only want to rename the column, drop the NOT NULL constraint, or change the data type, you can use the following set of commands:

Note: These commands have the potential to corrupt your database, so make sure you have a backup

PRAGMA writable_schema = 1;
UPDATE SQLITE_MASTER SET SQL = 'CREATE TABLE BOOKS ( title TEXT NOT NULL, publication_date TEXT)' WHERE NAME = 'BOOKS';
PRAGMA writable_schema = 0;

You will need to either close and reopen your connection or vacuum the database to reload the changes into the schema.

For example:

Y:\> sqlite3 booktest  
SQLite version 3.7.4  
Enter ".help" for instructions  
Enter SQL statements terminated with a ";"  
sqlite> create table BOOKS ( title TEXT NOT NULL, publication_date TEXT NOT NULL);  
sqlite> insert into BOOKS VALUES ("NULLTEST",null);  
Error: BOOKS.publication_date may not be NULL  
sqlite> PRAGMA writable_schema = 1; 
sqlite> UPDATE SQLITE_MASTER SET SQL = 'CREATE TABLE BOOKS ( title TEXT NOT NULL, publication_date TEXT)' WHERE NAME = 'BOOKS';  
sqlite> PRAGMA writable_schema = 0;  
sqlite> .q  

Y:\> sqlite3 booktest  
SQLite version 3.7.4  
Enter ".help" for instructions  
Enter SQL statements terminated with a ";"  
sqlite> insert into BOOKS VALUES ("NULLTEST",null);  
sqlite> .q  

REFERENCES FOLLOW:

alter table
SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows the user to rename a table or to add a new column to an existing table. It is not possible to rename a column, remove a column, or add or remove constraints from a table.

Solution 5:

Recently I had to do that in SQLite3 with a table named points with the colunms id, lon, lat. Erroneusly, when the table was imported, the values for latitude where stored in the lon column and viceversa, so an obvious fix would be to rename those columns. So the trick was:

create table points_tmp as select id, lon as lat, lat as lon from points;
drop table points;
alter table points_tmp rename to points;

I hope this would be useful for you!