4

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.

William Miller
  • 9,839
  • 3
  • 25
  • 46
Leo E
  • 709
  • 9
  • 16
  • https://stackoverflow.com/questions/30290880/rename-a-column-in-mysql Check this link – İsa GİRİŞKEN Apr 26 '19 at 15:14
  • 3
    What is your SQLite version? `RENAME COLUMN` works from version 3.25.1+ – forpas Apr 26 '19 at 15:18
  • Isa that link only works for mysql not sqlite – Leo E Apr 26 '19 at 16:08
  • @forpas version is 3.26 but just to be on safe side I am doing a reinstall from scratch based on [this](http://www.linuxfromscratch.org/blfs/view/svn/server/sqlite.html) – Leo E Apr 26 '19 at 16:18
  • 1
    Good. For my 3.27.2 version `RENAME COLUMN` works fine. – forpas Apr 26 '19 at 16:20
  • The problem seems to have been that I was running the queries using sqlitebrowser rather than sqlite from command line directly. sqlitebrowser does not necessarily use the latest installed sqlite version [link](https://github.com/sqlitebrowser/sqlitebrowser/issues/999) – Leo E Apr 26 '19 at 17:15
  • For anyone looking to upgrade sqlite from scratch, [this](https://linuxhint.com/upgrade-to-latest-sqlite3-on-centos7/) is a good safe guide. – Leo E Apr 26 '19 at 17:59

1 Answers1

1

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.
Krishna Manohar
  • 322
  • 1
  • 3
  • 14