SQLite cannot drop columns

sqlite.org

2 pointsJarred2 comments

Other databases can't drop columns either. In most cases (row-based databases such as the default storage on pgsql, microsoft, oracle, mysql etc), dropping a column, if that syntax is available, internally rebuilds the table with the column omitted.

The data are stored in row-major order (they are in sqlite too) and to drop a column, you need to effectively rewrite every existing row (at least, all the ones where that column is non-null)

Essentially, ALTER TABLE ... DROP COLUMN is just syntactic sugar, you can do the operation equally efficiently (albeit with more complexity) with other statements.

In any case, sqlite CAN at least drop indexes, which is something that some versions of MySQL did quite badly (without a table rebuild)

Reply on news.ycombinator.com