SQLite Forum

Feature Request: Allow overwriting existing table with new one

Feature Request: Allow overwriting existing table with new one

(1) By Richard PArkins (rparkins) on 2020-05-21 10:26:46 [source]

Currently the procedure for altering a table in ways that the ALTER TABLE statement doesn't directly support is very complicated. I would like to be able to say:-

ALTER TABLE <name> AS <select-statement>

where the select-statement can reference the original table, although it doesn't have to. Alternatively this could be done with an option to CREATE TABLE allowing overwrite of an existing table.

This should not be too difficult to implement. Essentially you make a new table, and atomically rename it over the old one, like "mv" in Unix/Linux. Handling triggers in the table or views and foreign keys that reference the original table is a bit tricky, but it may be good enough to support the legacy_alter_table behaviour. It would certainly be good enough for me.


I maintain an sqlite database manipulating program at https://github.com/rparkins999/sqliteman. It was originally a fork from https://github.com/pvanek/sqliteman, but I have fixed many bugs and added new features. It supports a GUI for an extended version of ALTER TABLE which allows deleting and reordering columns as well as adding and renaming them. I do it by a similar sequence to the twelve steps in the ALTER TABLE documentation, but I don't currently quite get it right because I don't handle triggers in the table. I handle indices, views, and foreign keys pointing into the table by using

PRAGMA legacy_alter_table = ON;

but this doesn't work if a referenced column is renamed or deleted. I have plans to do it properly one day, but it's a lot of work for a feature that I don't use myself.

I also like to keep my tables sorted. I know I can look at a sorted view, but I can't edit the rows in it without some messy trickery to force any changes that I try to make in the sorted view to actually occur in the real table. Since my databases are all small enough to fit in the (quite large) memory of a modern PC, it's easier to keep my tables sorted. To re-sort I do something like

PRAGMA legacy_alter_table = ON;
create table main__sorted as select * from sorted ; drop table main ;
alter table main__sorted rename to main;

The PRAGMA is needed to stop post-3.25 version of sqlite from throwing away the sorted view when the original table is dropped. Of course this doesn't handle triggers, but I don't use them.

Anyway it would be much nicer to be able to say