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

by

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.

More Related Contents:

  1. ALTER TABLE ADD COLUMN IF NOT EXISTS in SQLite
  2. Modify a Column’s Type in sqlite3
  3. How do I unlock a SQLite database?
  4. “Insert if not exists” statement in SQLite
  5. How to delete or add column in SQLITE?
  6. Sqlite LIMIT / OFFSET query
  7. Set start value for AUTOINCREMENT in SQLite
  8. How to use ROW_NUMBER in sqlite
  9. Android – Pulling SQlite database android device
  10. Insert new column into table in sqlite?
  11. How do you enable LIMIT for DELETE in SQLite?
  12. SQLite Schema Information Metadata
  13. How to create custom functions in SQLite
  14. Sqlite group_concat ordering
  15. sqlite alter table add MULTIPLE columns in a single statement
  16. is there a limit to the size of a SQLite database?
  17. How to generate all dates between two dates
  18. Does it matter what extension is used for SQLite database files?
  19. sqlite – How to get INSERT OR IGNORE to work
  20. How do I DROP a constraint from a sqlite (3.6.21) table?
  21. How do I add a new column in between two columns?
  22. Creating stored procedure and SQLite?
  23. How Scalable is SQLite? [closed]
  24. Are there any reserved words in SQLite?
  25. What is the difference between related SQLite data-types like INT, INTEGER, SMALLINT and TINYINT?
  26. Case sensitive and insensitive like in SQLite
  27. SQLite accent-insensitive search
  28. sql query – how to apply limit within group by
  29. Unable to load DLL sqlite3 in Universal Windows App running on Mobile
  30. Creating stored procedure in SQLite

Leave a Comment