How to delete or add column in SQLITE

Question

I want to delete or add column in sqlite database  I am using following query  to delete column   ALTER TABLE TABLENAME DROP COLUMN COLUMNNAME   But it gives error   System Data SQLite SQLiteException  SQLite error near  DROP   syntax error

User · Answer

DB Browser for SQLite allows you to add or drop columns.

In the main view, tab Database Structure, click on the table name. A button Modify Table gets enabled, which opens a new window where you can select the column/field and remove it.

User · Answer

We cannot drop a specific column in SQLite 3  See the FAQ

User · Answer

As others have pointed out  sqlite s ALTER TABLE statement does not support DROP COLUMN  and the standard recipe to do this does not preserve constraints  amp  indices   Here s some python code to do this generically  while maintaining all the key constraints and indices    Please back-up your database before using  This function relies on doctoring the original CREATE TABLE statement and is potentially a bit unsafe - for instance it will do the wrong thing if an identifier contains an embedded comma or parenthesis   If anyone would care to contribute a better way to parse the SQL  that would be great   UPDATE I found a better way to parse using the open-source sqlparse package  If there is any interest I will post it here  just leave a comment asking for it      import re import random  def DROP COLUMN db  table  column       columns     c 1  for c in db execute  PRAGMA table info  s     table        columns     c for c in columns if c    column       sql   db execute  SELECT sql from sqlite master where name     s              table  fetchone   0      sql   format sql      lines   sql splitlines       findcol   r  b s b    column     keeplines     line for line in lines if not re search findcol  line        create     n  join keeplines      create   re sub r    s       r  1   create      temp    tmp d    random randint 1e8  1e9      db execute  ALTER TABLE   old s RENAME TO   new s                old   table   new   temp        db execute create      db execute             INSERT INTO   new s     columns s            SELECT   columns s FROM   old s                       old   temp           new   table           columns        join columns               db execute  DROP TABLE  s    temp   def format sql       sql   sql replace         n       sql   sql replace         n       sql   sql replace        n        return sql

User · Answer

As others have pointed out      It is not possible to rename a column  remove a column  or add or   remove constraints from a table    source   http   www sqlite org lang altertable html  While you can always create a new table and then drop the older one   I will try to explain this workaround with an example   sqlite gt   schema CREATE TABLE person   id INTEGER PRIMARY KEY    first name TEXT   last name TEXT    age INTEGER    height INTEGER    sqlite gt  select   from person    id          first name  last name   age         height     ----------  ----------  ----------  ----------  ---------- 0           john        doe         20          170        1           foo         bar         25          171          Now you want to remove the column height from this table   Create another table called new person   sqlite gt  CREATE TABLE new person         gt   id INTEGER PRIMARY KEY          gt   first name TEXT          gt   last name TEXT          gt   age INTEGER         gt       sqlite gt     Now copy the data from the old table   sqlite gt  INSERT INTO new person        gt  SELECT id  first name  last name  age FROM person   sqlite gt  select   from new person   id          first name  last name   age        ----------  ----------  ----------  ---------- 0           john        doe         20         1           foo         bar         25         sqlite gt    Now Drop the person table and rename new person to person  sqlite gt  DROP TABLE IF EXISTS person    sqlite gt  ALTER TABLE new person RENAME TO person   sqlite gt    So now if you do a  schema  you will see  sqlite gt  schema CREATE TABLE  person    id INTEGER PRIMARY KEY    first name TEXT    last name TEXT    age INTEGER

User · Answer

You can use the SQlite Administrator for changing the column names  Right Click on Table name and select Edit Table Here you will find the table structure and you can easily rename it

User · Answer

ALTER TABLE SQLite     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    You can    create new table as the one you are trying to change  copy all data  drop old table  rename the new one

User · Answer

As SQLite has limited support to ALTER TABLE so you can only ADD column at end of the table OR CHANGE TABLE NAME in SQLite   Here is the Best Answer of HOW TO DELETE COLUMN FROM SQLITE   visit Delete column from SQLite table

User · Answer

SQLite 3 35 0 will introduce support for ALTER TABLE DROP COLUMN   ALTER TABLE The DROP COLUMN syntax is used to remove an existing column from a table  The DROP COLUMN command removes the named column from the table  and also rewrites the entire table to purge the data associated with that column  The DROP COLUMN command only works if the column is not referenced by any other parts of the schema and is not a PRIMARY KEY and does not have a UNIQUE constraint   The following syntax will be valid  ALTER TABLE  lt TABLENAME gt  DROP COLUMN  lt COLUMNNAME gt   ALTER TABLE  lt TABLENAME gt  DROP  lt COLUMNNAME gt

User · Answer

I have improved user2638929 answer and now it can preserves column type  primary key  default value etc  public static void dropColumns SQLiteDatabase database  String tableName  Collection lt String gt  columnsToRemove       List lt String gt  columnNames   new ArrayList lt  gt         List lt String gt  columnNamesWithType   new ArrayList lt  gt         List lt String gt  primaryKeys   new ArrayList lt  gt         String query    quot pragma table info  quot    tableName    quot    quot       Cursor cursor   database rawQuery query null       while  cursor moveToNext             String columnName   cursor getString cursor getColumnIndex  quot name quot              if  columnsToRemove contains columnName                continue                     String columnType   cursor getString cursor getColumnIndex  quot type quot             boolean isNotNull   cursor getInt cursor getColumnIndex  quot notnull quot       1          boolean isPk   cursor getInt cursor getColumnIndex  quot pk quot       1           columnNames add columnName           String tmp    quot   quot    columnName    quot    quot    columnType    quot   quot           if  isNotNull               tmp     quot  NOT NULL  quot                      int defaultValueType   cursor getType cursor getColumnIndex  quot dflt value quot             if  defaultValueType    Cursor FIELD TYPE STRING               tmp     quot  DEFAULT  quot     quot   quot  quot    cursor getString cursor getColumnIndex  quot dflt value quot       quot   quot   quot            else if defaultValueType    Cursor FIELD TYPE INTEGER               tmp     quot  DEFAULT  quot    cursor getInt cursor getColumnIndex  quot dflt value quot       quot   quot            else if  defaultValueType    Cursor FIELD TYPE FLOAT               tmp     quot  DEFAULT  quot    cursor getFloat cursor getColumnIndex  quot dflt value quot       quot   quot                     columnNamesWithType add tmp           if  isPk               primaryKeys add  quot   quot    columnName    quot   quot                        cursor close         String columnNamesSeparated   TextUtils join  quot    quot   columnNames       if  primaryKeys size    gt  0           columnNamesWithType add  quot PRIMARY KEY  quot   TextUtils join  quot    quot   primaryKeys    quot   quot              String columnNamesWithTypeSeparated   TextUtils join  quot    quot   columnNamesWithType        database beginTransaction        try           database execSQL  quot ALTER TABLE  quot    tableName    quot  RENAME TO  quot    tableName    quot  old  quot            database execSQL  quot CREATE TABLE  quot    tableName    quot    quot    columnNamesWithTypeSeparated    quot    quot            database execSQL  quot INSERT INTO  quot    tableName    quot    quot    columnNamesSeparated    quot   SELECT  quot                    columnNamesSeparated    quot  FROM  quot    tableName    quot  old  quot            database execSQL  quot DROP TABLE  quot    tableName    quot  old  quot            database setTransactionSuccessful         finally           database endTransaction             PS  I used here android arch persistence db SupportSQLiteDatabase  but you can easyly modify it for use android database sqlite SQLiteDatabase

User · Answer

You can also now use DB browser for SQLite to manipulate columns

User · Answer

As an alternative   If you have a table with schema  CREATE TABLE person    id INTEGER PRIMARY KEY    first name TEXT    last name TEXT    age INTEGER    height INTEGER      you can use a CREATE TABLE   AS statement like CREATE TABLE person2 AS SELECT id  first name  last name  age FROM person   i e  leave out the columns you don t want  Then drop the original person table and rename the new one   Note this method produces a table has no PRIMARY KEY and no constraints  To preserve those  utilize the methods others described to create a new table  or use a temporary table as an intermediate

User · Answer

I rewrote the  Udinic answer so that the code generates table creation query automatically  It also doesn t need ConnectionSource  It also has to do this inside a transaction   public static String getOneTableDbSchema SQLiteDatabase db  String tableName        Cursor c   db rawQuery               SELECT   FROM  sqlite master  WHERE  type     table  AND  name         tableName        null       String result   null      if  c moveToFirst              result   c getString c getColumnIndex  sql               c close        return result     public List lt String gt  getTableColumns SQLiteDatabase db  String tableName        ArrayList lt String gt  columns   new ArrayList lt  gt         String cmd    pragma table info     tableName             Cursor cur   db rawQuery cmd  null        while  cur moveToNext              columns add cur getString cur getColumnIndex  name                cur close         return columns     private void dropColumn SQLiteDatabase db  String tableName  String   columnsToRemove        db beginTransaction        try           List lt String gt  columnNamesWithoutRemovedOnes   getTableColumns db  tableName              Remove the columns we don t want anymore from the table s list of columns         columnNamesWithoutRemovedOnes removeAll Arrays asList columnsToRemove             String newColumnNamesSeparated   TextUtils join        columnNamesWithoutRemovedOnes           String sql   getOneTableDbSchema db  tableName              Extract the SQL query that contains only columns         String oldColumnsSql   sql substring sql indexOf      1  sql lastIndexOf                 db execSQL  ALTER TABLE     tableName     RENAME TO     tableName     old             db execSQL  CREATE TABLE      tableName           getSqlWithoutRemovedColumns oldColumnsSql  columnsToRemove                  db execSQL  INSERT INTO     tableName         newColumnNamesSeparated      SELECT     newColumnNamesSeparated     FROM     tableName     old             db execSQL  DROP TABLE     tableName     old             db setTransactionSuccessful          catch             Error in between database transaction        finally           db endTransaction

User · Answer

public void DeleteColFromTable String DbName  String TableName  String ColName       SQLiteDatabase db   openOrCreateDatabase    DbName     Context MODE PRIVATE  null       db execSQL  CREATE TABLE IF NOT EXISTS   TableName   1x00dff          Cursor c   db rawQuery  PRAGMA table info   TableName      null       if  c getCount      0           else           String columns1               String columns2               while  c moveToNext                  if  c getString 1  equals ColName                   else                   columns1   columns1          c getString 1          c getString 2                   columns2   columns2          c getString 1                             if  c isLast                      db execSQL  CREATE TABLE IF NOT EXISTS DataBackup      columns1                          db execSQL  INSERT INTO DataBackup SELECT     columns2     FROM   TableName                       db execSQL  DROP TABLE   TableName                      db execSQL  ALTER TABLE DataBackup RENAME TO   TableName                                         and just call a method  DeleteColFromTable  Database name   Table name   Col name which want to delete

User · Answer

This answer to a different question is oriented toward modifying a column  but I believe a portion of the answer could also yield a useful approach if you have lots of columns and don t want to retype most of them by hand for your INSERT statement    https   stackoverflow com a 10385666  You could dump your database as described in the link above  then grab the  create table  statement and an  insert  template from that dump  then follow the instructions in the SQLite FAQ entry  How do I add or delete columns from an existing table in SQLite    FAQ is linked elsewhere on this page

User · Answer

I guess what you are wanting to do is database migration   Drop ping a column does not exist in SQLite  But you can however  add an extra column by using the ALTER table query

User · Answer

Implementation in Python based on information at http   www sqlite org faq html q11   import sqlite3 as db import random import string  QUERY TEMPLATE GET COLUMNS    PRAGMA table info  table name   QUERY TEMPLATE DROP COLUMN         BEGIN TRANSACTION    CREATE TEMPORARY TABLE  tmp table  columns to keep     INSERT INTO  tmp table SELECT  columns to keep FROM  table name    DROP TABLE  table name    CREATE TABLE  table name  columns to keep     INSERT INTO  table name SELECT  columns to keep FROM  tmp table    DROP TABLE  tmp table    COMMIT       def drop column db file  table name  column name       con   db connect db file      QUERY GET COLUMNS   QUERY TEMPLATE GET COLUMNS replace   table name   table name      query res   con execute QUERY GET COLUMNS  fetchall       columns list to keep    i 1  for i in query res if i 1     column name      columns to keep       join columns list to keep      tmp table    tmp  s       join random sample string ascii lowercase  10       QUERY DROP COLUMN   QUERY TEMPLATE DROP COLUMN replace   table name   table name            replace   tmp table   tmp table  replace   columns to keep   columns to keep      con executescript QUERY DROP COLUMN      con close    drop column DB FILE  TABLE NAME  COLUMN NAME    This script first makes random temporary table and inserts data of only necessary columns except the one that will will be dropped  Then restores the original table based on the temporary table and drops the temporary table

User · Answer

I ve wrote a Java implementation based on the Sqlite s recommended way to do this   private void dropColumn SQLiteDatabase db          ConnectionSource connectionSource          String createTableCmd          String tableName          String   colsToRemove  throws java sql SQLException        List lt String gt  updatedTableColumns   getTableColumns tableName          Remove the columns we don t want anymore from the table s list of columns     updatedTableColumns removeAll Arrays asList colsToRemove         String columnsSeperated   TextUtils join      updatedTableColumns        db execSQL  ALTER TABLE     tableName     RENAME TO     tableName     old             Creating the table on its new format  no redundant columns      db execSQL createTableCmd           Populating the table with the data     db execSQL  INSERT INTO     tableName         columnsSeperated      SELECT                 columnsSeperated     FROM     tableName     old         db execSQL  DROP TABLE     tableName     old         To get the table s column  I used the  PRAGMA table info    public List lt String gt  getTableColumns String tableName        ArrayList lt String gt  columns   new ArrayList lt String gt         String cmd    pragma table info     tableName             Cursor cur   getDB   rawQuery cmd  null        while  cur moveToNext              columns add cur getString cur getColumnIndex  name                cur close         return columns      I actually wrote about it on my blog  you can see more explanations there   http   udinic wordpress com 2012 05 09 sqlite-drop-column-support

User · Answer

you can use Sqlitebrowser  In the browser mode  for the respective database and the table  under the tab -database structure following the option Modify Table  respective column could be removed

User · Answer

My solution  only need to call this method   public static void dropColumn SQLiteDatabase db  String tableName  String   columnsToRemove  throws java sql SQLException       List lt String gt  updatedTableColumns   getTableColumns db  tableName       updatedTableColumns removeAll Arrays asList columnsToRemove        String columnsSeperated   TextUtils join      updatedTableColumns        db execSQL  ALTER TABLE     tableName     RENAME TO     tableName     old         db execSQL  CREATE TABLE     tableName          columnsSeperated              db execSQL  INSERT INTO     tableName         columnsSeperated      SELECT                 columnsSeperated     FROM     tableName     old         db execSQL  DROP TABLE     tableName     old         And auxiliary method to get the columns   public static List lt String gt  getTableColumns SQLiteDatabase db  String tableName        ArrayList lt String gt  columns   new ArrayList lt  gt         String cmd    pragma table info     tableName             Cursor cur   db rawQuery cmd  null        while  cur moveToNext              columns add cur getString cur getColumnIndex  name                cur close         return columns

User · Answer

http   www sqlite org lang altertable html  As you can see in the diagram  only ADD COLUMN is supported  There is a  kinda heavy  workaround  though  http   www sqlite org faq html q11

User · Answer

Kotlin solution  based on here   but also ensures the temporary table doesn t already exist  object DbUtil        JvmStatic     fun dropColumns database  SQLiteDatabase  tableName  String  columnsToRemove  Collection lt String gt             val columnNames  MutableList lt String gt    ArrayList           val columnNamesWithType  MutableList lt String gt    ArrayList           val primaryKeys  MutableList lt String gt    ArrayList           val query    quot pragma table info  tableName   quot          val cursor   database rawQuery query  null          while  cursor moveToNext                  val columnName   cursor getString cursor getColumnIndex  quot name quot                if  columnsToRemove contains columnName                     continue                           val columnType   cursor getString cursor getColumnIndex  quot type quot                val isNotNull   cursor getInt cursor getColumnIndex  quot notnull quot       1             val isPk   cursor getInt cursor getColumnIndex  quot pk quot       1             columnNames add columnName              var tmp    quot   columnName   columnType  quot              if  isNotNull                    tmp     quot  NOT NULL  quot                            when  cursor getType cursor getColumnIndex  quot dflt value quot                       Cursor FIELD TYPE STRING - gt                        tmp     quot  DEFAULT   quot   cursor getString cursor getColumnIndex  quot dflt value quot      quot   quot                                    Cursor FIELD TYPE INTEGER - gt                        tmp     quot  DEFAULT   cursor getInt cursor getColumnIndex  quot dflt value quot      quot                                    Cursor FIELD TYPE FLOAT - gt                        tmp     quot  DEFAULT   cursor getFloat cursor getColumnIndex  quot dflt value quot      quot                                              columnNamesWithType add tmp              if  isPk                    primaryKeys add  quot   columnName  quot                                   cursor close           val columnNamesSeparated   TextUtils join  quot    quot   columnNames          if  primaryKeys size  gt  0                columnNamesWithType add  quot PRIMARY KEY   TextUtils join  quot    quot   primaryKeys    quot                     val columnNamesWithTypeSeparated   TextUtils join  quot    quot   columnNamesWithType          database beginTransaction           try               var newTempTableName  String             var counter   0             while  true                    newTempTableName    quot   tableName  old  counter quot                  if   isTableExists database  newTempTableName                       break                   counter                           database execSQL  quot ALTER TABLE  tableName RENAME TO  newTempTableName  quot               database execSQL  quot CREATE TABLE  tableName   columnNamesWithTypeSeparated   quot               database execSQL  quot INSERT INTO  tableName   columnNamesSeparated  SELECT  columnNamesSeparated FROM  newTempTableName  quot               database execSQL  quot DROP TABLE   newTempTableName   quot               database setTransactionSuccessful             finally               database endTransaction                         JvmStatic     fun isTableExists database  SQLiteDatabase  tableName  String   Boolean           database rawQuery  quot select DISTINCT tbl name from sqlite master where tbl name     tableName  quot   null   use               return it count  gt  0              return false

[sqlite] How to delete or add column in SQLITE?

Examples related to sqlite