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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

change table column  lt  id   to  lt   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

User · Answer

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

User · Answer

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

User · Answer

sqlite3 yourdb  dump    tmp db txt  edit  tmp db txt change column name in Create line sqlite2 yourdb2  lt   tmp db txt mv move yourdb2 yourdb

User · Answer

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    Start a transaction  Run PRAGMA schema version to determine the current schema version number  This number will be needed for step 6 below  Activate schema editing using PRAGMA writable schema ON  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  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  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  Disable schema editing using PRAGMA writable schema OFF   Optional  Run PRAGMA integrity check to verify that the schema changes did not damage the database  Commit the transaction started on step 1 above

User · Answer

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   gt  sqlite3 booktest   SQLite version 3 7 4   Enter   help  for instructions   Enter SQL statements terminated with a       sqlite gt  create table BOOKS   title TEXT NOT NULL  publication date TEXT NOT NULL     sqlite gt  insert into BOOKS VALUES   NULLTEST  null     Error  BOOKS publication date may not be NULL   sqlite gt  PRAGMA writable schema   1   sqlite gt  UPDATE SQLITE MASTER SET SQL    CREATE TABLE BOOKS   title TEXT NOT NULL  publication date TEXT   WHERE NAME    BOOKS     sqlite gt  PRAGMA writable schema   0    sqlite gt   q    Y   gt  sqlite3 booktest   SQLite version 3 7 4   Enter   help  for instructions   Enter SQL statements terminated with a       sqlite gt  insert into BOOKS VALUES   NULLTEST  null     sqlite gt   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

User · Answer

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

User · Answer

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

User · Answer

Since version 2018-09-15  3 25 0  sqlite supports renaming columns  https   sqlite org changes html

User · Answer

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

[sqlite] How do I rename a column in a SQLite database table?

Examples related to sqlite

Examples related to alter-table