Rename column in sqlite

Question

I would like to rename a column in sqlite. I created two word titles with whitespace for some columns which creates issues later on (e.g. first name instead of first_name.

Previously, it seems that this was not possible link. But a release from a few months ago seems to have included the RENAME COLUMN option link.

This does not seem to work however.

ALTER TABLE tablename RENAME COLUMN first name TO first_name

The query produces the following error message :

near "COLUMN": syntax error: ALTER TABLE tablename RENAME COLUMN

I have including quotation marks for the column name in case whitespace was the issue :

ALTER TABLE tablename RENAME COLUMN "first name" TO "first_name"

But get the same error.

This solution hints that renaming is possible. But only renames tables (which works fine) but not columns.

Answer 1

Update 2024

From How do I add, delete or rename columns from an existing table in SQLite?

SQLite has limited ALTER TABLE support that you can use to add, rename or drop columns or to change the name of a table as detailed at ALTER TABLE.

If you want to make more complex changes in the structure or constraints of a table or its columns, you will have to recreate it. ...

Old version

First of all Sqlite has this drawback that, we cannot rename the columns. Rather we have to create a new table with with desired Column names and then copy data from old table.

There is a work around to rename the table manually using the DB SQLite Browsers, as follows

1. Open the database file (.sqlite or .sqlite3) in your DB Browser, I used a sample DB with table name "RenameDemo"
2. Right Click on the table name in which you want to change column name. In the displayed options select Modify Table
3. An UI will appear where you can edit the table definition, in this window, double click on the column name which will make it editable.
4. Enter new name to the column. Click Ok.
5. In the main screen click ctrl+s command to commit these changes to the DB.