Delete column from SQLite table

Question

Update: SQLite 2021-03-12 (3.35.0) now supports DROP COLUMN. The FAQ on the website is still outdated.

---

From: http://www.sqlite.org/faq.html:

(11) How do I add or delete columns from an existing table in SQLite.

SQLite has limited ALTER TABLE support that you can use to add a column to the end of a table or to change the name of a table. If you want to make more complex changes in the structure of a table, you will have to recreate the table. You can save existing data to a temporary table, drop the old table, create the new table, then copy the data back in from the temporary table.

For example, suppose you have a table named "t1" with columns names "a", "b", and "c" and that you want to delete column "c" from this table. The following steps illustrate how this could be done:

BEGIN TRANSACTION;
CREATE TEMPORARY TABLE t1_backup(a,b);
INSERT INTO t1_backup SELECT a,b FROM t1;
DROP TABLE t1;
CREATE TABLE t1(a,b);
INSERT INTO t1 SELECT a,b FROM t1_backup;
DROP TABLE t1_backup;
COMMIT;

Instead of dropping the backup table, just rename it...

BEGIN TRANSACTION;
CREATE TABLE t1_backup(a,b);
INSERT INTO t1_backup SELECT a,b FROM t1;
DROP TABLE t1;
ALTER TABLE t1_backup RENAME TO t1;
COMMIT;

For simplicity, why not create the backup table from the select statement?

CREATE TABLE t1_backup AS SELECT a, b FROM t1;
DROP TABLE t1;
ALTER TABLE t1_backup RENAME TO t1;

This option works only if you can open the DB in a DB Browser like DB Browser for SQLite.

In DB Browser for SQLite:

1. Go to the tab, "Database Structure"
2. Select you table Select Modify table (just under the tabs)
3. Select the column you want to delete
4. Click on Remove field and click OK

=>Create a new table directly with the following query:

CREATE TABLE table_name (Column_1 TEXT,Column_2 TEXT);

=>Now insert the data into table_name from existing_table with the following query:

INSERT INTO table_name (Column_1,Column_2) FROM existing_table;

=>Now drop the existing_table by following query:

DROP TABLE existing_table;

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE table1 RENAME TO _table1_old;

CREATE TABLE table1 (
( column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
);

INSERT INTO table1 (column1, column2, ... column_n)
  SELECT column1, column2, ... column_n
  FROM _table1_old;

COMMIT;

PRAGMA foreign_keys=on;

For more info: https://www.techonthenet.com/sqlite/tables/alter_table.php

I've made a Python function where you enter the table and column to remove as arguments:

def removeColumn(table, column):
    columns = []
    for row in c.execute('PRAGMA table_info(' + table + ')'):
        columns.append(row[1])
    columns.remove(column)
    columns = str(columns)
    columns = columns.replace("[", "(")
    columns = columns.replace("]", ")")
    for i in ["\'", "(", ")"]:
        columns = columns.replace(i, "")
    c.execute('CREATE TABLE temptable AS SELECT ' + columns + ' FROM ' + table)
    c.execute('DROP TABLE ' + table)
    c.execute('ALTER TABLE temptable RENAME TO ' + table)
    conn.commit()

As per the info on Duda's and MeBigFatGuy's answers this won't work if there is a foreign key on the table, but this can be fixed with 2 lines of code (creating a new table and not just renaming the temporary table)