Copying a table doesn't recreate the autoincrement Primary Key

Databases
1

An SQLite question. My app copies a database table using the following:

sql = "CREATE TABLE newtable AS SELECT * FROM oldtable"

My problem is that oldtable has a column called ID that I have set as Unique Primary Key Autoincrement. The above code does create the new table, with the same structure as the old, but the new ID column is not set to Autoincrement.

How do I copy an old table to a new, while maintaining an ID column that autoincrements?

2

Have you tried

s = "SELECT * INTO newtable FROM oldtable;"

Don’t know if it will give the desired result, but worth a try.

3

Autoincrement, Default values , Primary Key attributes are part of the CREATE TABLE syntax

Since you used the minimal syntax, you get the minimal results.

Try a CREATE TABLE statement similar to what was used to create the old table
then a second statement to do an INSERT INTO

4

As an alternative, you may be interested in the “CREATE TABLE new (LIKE old …” syntax. As an example for PostgreSQL see https://www.postgresql.org/docs/current/static/sql-createtable.html

5
  1. read the sql use to create the original table from sqlite_master (select sql from sqlite_master where type = ‘table’ and name = ‘YOUR ORIGINAL TABLE NAME HERE’)
  2. rename the original table (alter table YOUR ORIGINAL TABLE NAME HERE rename to YOUR TABLE NAME COPY)
  3. run the sql to recreate the original exactly as it was - use sql execute to rerun the CREATE TABLE saved in #1
  4. insert all the data back into it (insert into YOUR ORIGINAL TABLE NAME select * from YOUR TABLE NAME COPY)

should recreate the original table exactly as it was originally defined with all the data

He’s using sqlite

6

oh. I did verify that the feature is actually in the standard and not an PostgreSQL extension but obviously failed to realise that the question was SQLite specific, which apparently does not support this syntax. Sorry for the noise.