1

I am trying to find the best way to delete a column in SQLite. Here are the two ways I found:

From the official website http://www.sqlite.org/faq.html#q11:

BEGIN TRANSACTION;
CREATE TEMPORARY TABLE t1_backup(a,b);
INSERT INTO t1_backup SELECT a,b FROM t1;
DROP TABLE t1;
CREATE TABLE t1(a,b);
INSERT INTO t1 SELECT a,b FROM t1_backup;
DROP TABLE t1_backup;
COMMIT;

and from How to delete or add column in SQLITE? (which is also from the official website http://sqlite.org/lang_altertable.html):

 1. create new table as the one you are trying to change, 
 2. copy all data,
 3. drop old table,
 4. rename the new one.

The first method uses a temporary table as the middle man to hold the values. The second method directly copies the values to the new table and renaming table name later. From a top level view, these are the only differences I see. Which one is more efficient? Is there an advantage choosing one over the other?

Is there any underlying information that I should take note of like rollback safe?

Community
  • 1
  • 1
kyrax
  • 1,192
  • 3
  • 13
  • 29
  • In my opinion the second one is more efficient as there is only one `INSERT` – cha Sep 03 '14 at 23:21
  • If doing the 2nd method, I would rename old table first, then create the new table, then copy. The drop is then optional, and can perhaps be done after confirmation that the new table is working as expected. – Turophile Sep 04 '14 at 08:00

1 Answers1

0

Using a temporary table might be slower because all the data must be copied twice. However, creating a new table and then renaming it needs more space for the actual database (which needs to hold the old and new tables at the same time), while the temporary table will be stored in the temporary database.

Both methods are perfectly safe when run inside a transaction.

CL.
  • 173,858
  • 17
  • 217
  • 259
  • Wouldn't the first method also need room for two copies of the table (although one is declared as a temporary table). – Turophile Sep 04 '14 at 08:02
  • @Turophile From what I read, a database's size either grows or stays the same until you clean it up. Creating a new table in the non-temporary database would increase the size of it, whereas the temporary table don't because it's stored in the temporary database. http://stackoverflow.com/questions/2143800/change-sqlite-file-size-after-delete-from-table – kyrax Sep 04 '14 at 23:19
  • You could be right - I'm no expert on sqlite which could well be different to other databases in how it manages storage. – Turophile Sep 05 '14 at 06:30