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

Question

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.

Firenze

You can do it using db browser for sqlite pretty easily – Selfdriven

Please consider marking this answer as accepted https://mcmap.net/q/98251/-how-do-i-rename-a-column-in-a-sqlite-database-table – Lapful

Answer 1

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.

Procne

I'm implementing for an Android migration and unfortunately IntelliJ is showing a warning that it is not a valid SQL command. database.execSQL("ALTER TABLE content RENAME COLUMN archiveCount TO dismissCount"). COLUM is higlighted in red and it says TO expected, got 'COLUMN'. Unfortunately Android is still on SQLite version 3.19 which is why this does not work for me. – Ingot

I have trided the latest System.Data.SQLite (1.0.109.2) NuGet package in a test .NET (ver 4.7.2) console application , but without any success - got SQL logic error near "COLUMN": syntax error. Isn't that version 3.25.0 version part of the nuget package yet? – Avant

edited: I have found on system.data.sqlite.org/index.html/doc/trunk/www/faq.wiki#q1 ,that the 1.0.109.x) is actually using SQLite 3.24 and the System.Data.SQLite using SQLite 3.25 is cheduled to be relased this month. – Avant

FYI, unfortunately this has yet to be implemented by Android's SQLite library. Hopefully they will update soon. – Ingot

I added a section for Android Support to prevent others from getting their hopes up. Based on Android 27's current usage of SQLite 3.19, we will have to wait until roughly API 33 before this feature gets added to Android, and even then it'll only be supported on the latest versions. Sigh. – Mccullum

@JoshuaPinter Thanks for extending my answer. – Procne

Anyway I can rename column name with query at the moment? I also need this in my Migration like @Ingot – Litchi

Is it just me or is that example in the answer completely off? Isn't that renaming a table rather than a column? – Nonparous

@Nonparous Thank you very much. Yes, you are right and I just adjusted the example :) – Procne

Answer 2

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

Example to rename Really Bad : Column Name to BetterColumnName:

ALTER TABLE your_table
RENAME COLUMN "Really Bad : Column Name" TO BetterColumnName

According to keywords the use of "double-quotes" is the standard way

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.

Gamelan

And don't forget your indices. – Eckel

Very importantly the example code above is missing a transaction. You should wrap the whole thing in a BEGIN/END (or ROLLBACK) to ensure that the renaming either completes successfully or not at all. – Synesthesia

How should one deal with in terms of indicies? – Oriane

After doing the insert, re-create your indices exactly the same way you did when you first created the table (adding new ones if relevant). – Gamelan

Anyone wishing to do this in android can implement transactions using SQLiteDatabase.beginTransaction() – Elastin

Using BEGIN TRANSACTION; and COMMIT; didn't worked for me. Neither in SQLite Administrator nor in sqliteman. Removing both and my query was successful. – Isleen

@ThomasG.Mayfield what do you mean "don't forget your indices."? The answer appears well received and accurate, but your comment comes recommended and lead me to begin rebuilding my database from scratch tonight. – Gurge

You just need to remember to re-apply all your indices to the new table. – Eckel

@ThomasG.Mayfield I'm confused as well about the indices comment. Aren't indices copied verbatim just like all of the other columns which are not renamed? – Elmaelmajian

There's nothing in the code in the answer that copies indices. Creating an empty table and putting data into it only copies structure and data. If you want metadata (indices, foreign keys, constraints, etc.), then you also have to issue statements to create them on the replaced table. – Eckel

SQLite's .schema command is handy for showing the CREATE TABLE statement that makes the existing table. You can take its output, modify as needed, and execute it to create the new table. This command also shows the necessary CREATE INDEX commands to create the indices, which should cover Thomas's concerns. Of course, be sure to run this command before altering anything. – Erlineerlinna

This is exactly what DB Browser for SQLite (sourceforge.net/projects/sqlitebrowser) does as well. You can know because it prints the SQL commands it executes for the edits you do through its GUI. – Henryson

Isn't it better to load all data into memory, drop the old table, re-create it with the new definition, and put all data back to it (all inside a transaction) ? – Baalbek

@MikeDeSimone If I recreate the new table with an index on any columns for which the previous (now temp) table had, will the new table's indexes be built as I run the INSERT INTO commands to migrate the old data? – Profusive

@mattblang I'd expect them to be, just like any other case where you're inserting into a table with indexes. – Erlineerlinna

What about VACUUM; after dropping the old table? – Kuchen

Renaming the old table is not advised. The official SQLite documentation here (sqlite.org/lang_altertable.html#otheralter) states: "[...] the initial rename of the table to a temporary name might corrupt references to that table in triggers, views, and foreign key constraints. The safe procedure on the left constructs the revised table definition using a new temporary name, then renames the table into its final name, which does not break links." – Liqueur

Answer 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!

Firenze

There is also a Firefox add-on that does the same thing, Right click the column you want to rename and select "Edit Column". – Isley

Even in openSUSE, it is available as a package: software.opensuse.org/package/sqlitebrowser – Henryson

It's weird it has so many votes. We're talking about programming here (code). Why did you even post this answer here? – Reeve

There's no mention of how to do this with code in my question. I just wanted to know how to rename a column in an SQLite DB. – Firenze

@Firenze I love you!!! (like a brother) got me in changed the field, voila. I had exported an MS Access table to SQLite and one of the field had a digit in front: 3YearLetterSent. Visual Studio made the class from the table but choked on the "3" digit at the front of the field name. I've know this, just wasn't watching. – Abut

On my Linux/Ubuntu I used sudo apt install sqlitebrowser. After changes make sure you quit the program and select update changes. – Cahier

Answer 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:

---

pragma writable_schema
When this pragma is on, the SQLITE_MASTER tables in which database can be changed using ordinary UPDATE, INSERT, and DELETE statements. Warning: misuse of this pragma can easily result in a corrupt database file.

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.

Conure

Dangerous, but still probably the most straight-forward answer imo. – Tinct

Although this is dangerous, would it be the best-performing way of renaming a column (better than making a new table, copying the data, and deleting the old table)? Also, is it really that dangerous? It seems prone to mistakes, but unless I'm mistaken, the UPDATE statement is wrapped in a transaction, so it should be safe from sudden power failures, out-of-memory exceptions, etc. – Progressionist

Yes extremely fast -- Dangerous only means "Make sure you have a backup first" – Conure

The sqlite file format is very simple and that's why this operation is valid. The file format has only two sets of information about a table: The actual CREATE TABLE command as plain text, and the rows, whose values are appearing in the order of the fields from the CREATE command. Which means that the sqlite code opens the database, it parses each CREATE command and dynamically builds its column information in memory. So, any command that alters the CREATE command in a way that ends up with the same number of columns will work, even if you change their type or constraints. – Hyrcania

@ThomasTempelmann However, adding constraints that are not fulfilled by the dataset will yield to problems because the query planner assumes that constraints hold. – Breckenridge

@FUZxxl Right, I ran into this myself recently. So I stand corrected: Changing constraints may not be safe using the method of altering the CREATE command directly. – Hyrcania

@ThomasTempelmann Removing constraints is always fine. Adding constraints is fine if the constraint is satisfied by all rows but you certainly need to check. – Breckenridge

If you have a large database, especially one that is larger than available disk space, this is the only solution and will be a hell of a lot faster. Do find a way to make a backup though! – Wheelbase

Answer 5

CASE 1 : SQLite 3.25.0+

Only the Version 3.25.0 of SQLite supports renaming columns. If your device is meeting this requirement, things are quite simple. The below query would solve your problem:

ALTER TABLE "MyTable" RENAME COLUMN "OldColumn" TO "NewColumn";

CASE 2 : SQLite Older Versions

You have to follow a different Approach to get the result which might be a little tricky

For example, if you have a table like this:

CREATE TABLE student(Name TEXT, Department TEXT, Location TEXT)

And if you wish to change the name of the column Location

Step 1: Rename the original table:

ALTER TABLE student RENAME TO student_temp;

Step 2: Now create a new table student with correct column name:

CREATE TABLE student(Name TEXT, Department TEXT, Address TEXT)

Step 3: Copy the data from the original table to the new table:

INSERT INTO student(Name, Department, Address) SELECT Name, Department, Location FROM student_temp;

Note: The above command should be all one line.

Step 4: Drop the original table:

DROP TABLE student_temp;

With these four steps you can manually change any SQLite table. Keep in mind that you will also need to recreate any indexes, viewers or triggers on the new table as well.

Coauthor

How to upgrade sqllite database version to 3.29.0 in android studio i'm using api level 28. – Tallou

SQLite version is defined by the device on which the App works. It is device dependent. – Coauthor

For people using old sqlite, the four steps above are discouraged. See the "Caution" section at sqlite.org/lang_altertable.html. – Cavendish

Answer 6

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!

Gourmet

This method does not copy the PK value appropriately and automatically creates the hidden rowid column. Not necessarily a problem but wanted to point that out because it became an issue for me. – Trituration

Wouldn't it be easier to do "UPDATE points SET lon = lat, lat = lon;"? – Mopey

This answer does do the process in the correct ORDER. First create the temp table and populate it then destroy the original. – Soubrette

Answer 7

Quoting the sqlite documentation:

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 colum, remove a column, or add or remove constraints from a table.

What you can do of course is, create a new table with the new layout, SELECT * FROM old_table, and fill the new table with the values you'll receive.

Janelljanella

Answer 8

First off, this is one of those things that slaps me in the face with surprise: renaming of a column requires creating an entirely new table and copying the data from the old table to the new table...

The GUI I've landed on to do SQLite operations is Base. It's got a nifty Log window that shows all the commands that have been executed. Doing a rename of a column via Base populates the log window with the necessary commands:

These can then be easily copied and pasted where you might need them. For me, that's into an ActiveAndroid migration file. A nice touch, as well, is that the copied data only includes the SQLite commands, not the timestamps, etc.

Hopefully, that saves some people time.

Mccullum

FYI, if you are using ActiveAndroid, you can omit the BEGIN TRANSACTION; and COMMIT; lines, as ActiveAndroid handles that by itself. – Mccullum

Answer 9

change table column < id > to < _id >

 String LastId = "id";

    database.execSQL("ALTER TABLE " + PhraseContract.TABLE_NAME + " RENAME TO " + PhraseContract.TABLE_NAME + "old");
    database.execSQL("CREATE TABLE " + PhraseContract.TABLE_NAME
    +"("
            + PhraseContract.COLUMN_ID + " INTEGER PRIMARY KEY,"
            + PhraseContract.COLUMN_PHRASE + " text ,"
            + PhraseContract.COLUMN_ORDER  + " text ,"
            + PhraseContract.COLUMN_FROM_A_LANG + " text"
    +")"
    );
    database.execSQL("INSERT INTO " +
            PhraseContract.TABLE_NAME + "("+ PhraseContract.COLUMN_ID +" , "+ PhraseContract.COLUMN_PHRASE + " , "+ PhraseContract.COLUMN_ORDER +" , "+ PhraseContract.COLUMN_FROM_A_LANG +")" +
            " SELECT " + LastId +" , "+ PhraseContract.COLUMN_PHRASE + " , "+ PhraseContract.COLUMN_ORDER +" , "+ PhraseContract.COLUMN_FROM_A_LANG +
            " FROM " + PhraseContract.TABLE_NAME + "old");
    database.execSQL("DROP TABLE " + PhraseContract.TABLE_NAME + "old");

Scandura

Answer 10

Create a new column with the desired column name: COLNew.

ALTER TABLE {tableName} ADD COLUMN COLNew {type};

Copy contents of old column COLOld to new column COLNew.

INSERT INTO {tableName} (COLNew) SELECT {COLOld} FROM {tableName}

Note: brackets are necessary in above line.

Lowspirited

Answer 11

As mentioned before, there is a tool SQLite Database Browser, which does this. Lyckily, this tool keeps a log of all operations performed by the user or the application. Doing this once and looking at the application log, you will see the code involved. Copy the query and paste as required. Worked for me. Hope this helps

Poulos

Answer 12

From the official documentation

A simpler and faster procedure can optionally be used for some changes that do no affect the on-disk content in any way. The following simpler procedure is appropriate for removing CHECK or FOREIGN KEY or NOT NULL constraints, renaming columns, or adding or removing or changing default values on a column.

1. Start a transaction.

2. Run PRAGMA schema_version to determine the current schema version number. This number will be needed for step 6 below.

3. Activate schema editing using PRAGMA writable_schema=ON.

4. Run an UPDATE statement to change the definition of table X in the sqlite_master table: UPDATE sqlite_master SET sql=... WHERE type='table' AND name='X';

   Caution: Making a change to the sqlite_master table like this will render the database corrupt and unreadable if the change contains a syntax error. It is suggested that careful testing of the UPDATE statement be done on a separate blank database prior to using it on a database containing important data.

5. If the change to table X also affects other tables or indexes or triggers are views within schema, then run UPDATE statements to modify those other tables indexes and views too. For example, if the name of a column changes, all FOREIGN KEY constraints, triggers, indexes, and views that refer to that column must be modified.

   Caution: Once again, making changes to the sqlite_master table like this will render the database corrupt and unreadable if the change contains an error. Carefully test of this entire procedure on a separate test database prior to using it on a database containing important data and/or make backup copies of important databases prior to running this procedure.

6. Increment the schema version number using PRAGMA schema_version=X where X is one more than the old schema version number found in step 2 above.

7. Disable schema editing using PRAGMA writable_schema=OFF.

8. (Optional) Run PRAGMA integrity_check to verify that the schema changes did not damage the database.

9. Commit the transaction started on step 1 above.

Misinform

PRAGMA integrity_check doesn't pick up any errors with the schema. – Swerve

and what is the problem with that? – Misinform

Answer 13

One option, if you need it done in a pinch, and if your initial column was created with a default, is to create the new column you want, copy the contents over to it, and basically "abandon" the old column (it stays present, but you just don't use/update it, etc.)

ex:

alter table TABLE_NAME ADD COLUMN new_column_name TYPE NOT NULL DEFAULT '';
update TABLE_NAME set new_column_name = old_column_name;
update TABLE_NAME set old_column_name = ''; -- abandon old column, basically

This leaves behind a column (and if it was created with NOT NULL but without a default, then future inserts that ignore it might fail), but if it's just a throwaway table, the tradeoffs might be acceptable. Otherwise use one of the other answers mentioned here, or a different database that allows columns to be renamed.

Medication

Answer 14

Since version 2018-09-15 (3.25.0) sqlite supports renaming columns

https://sqlite.org/changes.html

Allx

Answer 15

need to rename a few columns in some tables

Another way is to use multiple SQLite3 commands to "rename" a column, in "some" tables, repeat as needed:

.output tmp

SELECT "ALTER TABLE """|| sqlite_master.name ||""" RENAME COLUMN old_name TO new_name;" FROM sqlite_master 
WHERE type = "table" AND sqlite_master.name NOT LIKE 'sqlite_%';

.read tmp

source

Nevanevada

Answer 16

sqlite3 yourdb .dump > /tmp/db.txt
edit /tmp/db.txt change column name in Create line
sqlite2 yourdb2 < /tmp/db.txt
mv/move yourdb2 yourdb

Ed

you're answer doesn't provide any information, a bunch of code/instructions spit out without any extra information on why you think it will work or whats suppose to happen if you run it – Magna