24

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?

halfer
  • 19,824
  • 17
  • 99
  • 186
user938363
  • 9,990
  • 38
  • 137
  • 303

4 Answers4

41

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

Filipe Silva
  • 21,189
  • 5
  • 53
  • 68
  • For command of insert into tabelB above, does it keep the id as well? We need the record in new table having the same id as it was in tableA. – user938363 Dec 13 '13 at 20:19
  • Sure. if one of those columns is the id, it will be migrated to the new table. – Filipe Silva Dec 13 '13 at 20:26
  • 1
    @FilipeSilva What if the ID column is set to AUTOINCREMENT? – pavitran Mar 21 '17 at 09:00
  • If the foreign key of other tables is referencing this table one has to remove the foreign key reference before dropping the table . Check [here](https://stackoverflow.com/questions/3359329/how-to-change-the-foreign-key-referential-action-behavior) – M.C. Jun 27 '18 at 12:52
10

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.

BWS
  • 3,786
  • 18
  • 25
  • Need to match schema of 2 tables so that the data can be copied from one to another. Sqlite3 import does not match the column when copy. So we end up with integer column under text column for example. – user938363 Dec 13 '13 at 20:17
  • Well, in "INSERT" statement, it allows to put the VALUES in a order that explicitly matching the columns. – Kenneth May 21 '23 at 09:23
10

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 its own table, and our app is happy.

adius
  • 13,685
  • 7
  • 45
  • 46
user8353681
  • 101
  • 1
  • 3
  • 5
    This is very interesting, though it doesn't at all answer the question. – sapht Aug 10 '17 at 10:24
  • 4
    True. Filipe Silva's answer does answer the question. My answer was more about why this does need to be done, i.e. validates the question. We had to do it, and there was not a trivial way to change the order, so we created a second table and put the slow column in that table. This made a huge difference in performance for us. – user8353681 Aug 10 '17 at 15:03
  • Indeed, the answer doesn't (directly) answer the question. But it's a very valuable input. – cezar Oct 02 '18 at 12:07
  • I wondered if a similar performance difference would happen when placing long text columns at the end of the record so that smaller columns wouldn't have to "step over" the text columns. I ran just one test with the same records in two different tables but there was no difference when doing a select of the rightmost integer column. – Gary Z Jan 02 '19 at 02:26
-1

you can reorder them using the Sqlite Browser

Shawn
  • 3,031
  • 4
  • 26
  • 53