Sqlite3: how to reorder columns in a table?

Question

It seems that it is not straightforward for reordering columns in a sqlite3 table. At least the sqlite manager in firefox does not support this feature. For example, move the column2 to column3 and move column5 to column2. Is there a way to reorder columns in sqlite table, either with a sqlite management software or a script? Thanks.

Answer 1

This isn't a trivial task in any DBMS. You would almost certainly have to create a new table with the order that you want, and move your data from one table to the order. There is no alter table statement to reorder the columns, so either in sqlite manager or any other place, you will not find a way of doing this in the same table.

If you really want to change the order, you could do:

Assuming you have tableA:

create table tableA(
col1 int,
col3 int,
col2 int);

You could create a tableB with the columns sorted the way you want:

create table tableB(
col1 int,
col2 int,
col3 int);

Then move the data to tableB from tableA:

insert into tableB
SELECT col1,col2,col3 
FROM tableA;

Then remove the original tableA and rename tableB to TableA:

DROP table tableA;
ALTER TABLE tableB RENAME TO tableA;

sqlfiddle demo

Answer 2

The order in sqlite3 does matter. Conceptually, it shouldn't, but try this experiment to prove that it does:

CREATE TABLE SomeItems (identifier INTEGER PRIMARY KEY NOT NULL, filename TEXT NOT NULL, path TEXT NOT NULL, filesize INTEGER NOT NULL, thumbnail BLOB, pickedStatus INTEGER NOT NULL, deepScanStatus INTEGER NOT NULL, basicScanStatus INTEGER NOT NULL, frameQuanta INTEGER, tcFlag INTEGER, frameStart INTEGER, creationTime INTEGER);

Populate the table with about 20,000 records where thumbnail is a small jpeg. Then do a couple of queries like this:

time sqlite3 Catalog.db 'select count(*) from SomeItems where filesize = 2;'
time sqlite3 Catalog.db 'select count(*) from SomeItems where basicscanstatus = 2;'

Does not matter how many records are returned, on my machine, the first query takes about 0m0.008s and the second query takes 0m0.942s. Massive difference, and the reason is because of the Blob; filesize is before the Blob and basicscanstatus is after.

We've now moved the Blob into it's own table, and our app is happy.

Answer 3

You can always order the columns however you want to in your SELECT statement, like this:

SELECT column1,column5,column2,column3,column4
FROM mytable
WHERE ...

You shouldn't need to "order" them in the table itself.