I recently had someone ask me how to reorder the columns in a SQLite table. As you may be aware, the SQLite ALTER table does not have a lot of functionality compared to other databases and it certainly doesn’t have a way to do this.
But it turns out that reordering columns on a table is not really a supported operation for most databases. The reason is pretty simple I suppose: the order does not matter.
When you are querying the data in the table, you can request the columns in any order you want by listing them out in your preferred order in the SELECT statement. The same when you are using INSERT or UPDATE: you specify the order.
But some people like their table definitions to be in a specific order, perhaps for when viewing in admin tools. What do these people do?
The only solution I’ve found is to create a new table with the columns defined in the order you want and then copy the data from the original table to the new table. After you verify that the new table is correct, you remove (or rename) the original table and then rename the new table with the correct column order to the name of the original table.
This StackOverflow answer has more information: Sqlite3: how to reorder columns in a table?
