Drop a column in a SQLite table

Posted by robbiebow on 13 October, 2009 under geek | Be the First to Comment

SQLite doesn’t support DROP COLUMN. This means you need to do something slightly more complex to drop a column:

BEGIN TRANSACTION;
CREATE TABLE t1_new (
 foo  TEXT PRIMARY KEY,
 bar  TEXT,
 baz  INTEGER,
);

INSERT INTO t1_new SELECT foo, bar, baz FROM t1;
DROP TABLE t1;
ALTER TABLE t1_new RENAME TO t1;
COMMIT;

The quick way to get the SQL to create your new table would be to use the .schema command when connected to the database using the command line client.