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.

Answer 1

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.

Answer 2

Digging around, I found this multiplatform (Linux | Mac | Windows) graphical tool called SQLite Database Browser (how unsexy!) 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!

Answer 3

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;