clone table: INTEGER columns become INT
(1) By frank10 on 2022-11-09 17:39:27 [link] [source]
I want to clone a table on a DB.
I did:
create TABLE newT AS SELECT * FROM oldT;In oldT, most columns are INTEGER, other ones are TEXT.
In the new table I got all INTEGER columns as INT and I must change them manually to INTEGER. Why? Is there a way to leave type columns as declared in the original table? Without declaring one by one...
(2) By Stephan Beal (stephan) on 2022-11-09 17:42:34 in reply to 1 [link] [source]
In the new table I got all INTEGER columns as INT and I must change them manually to INTEGER.
You don't have to. They are equivalent, as documented at:
https://www.sqlite.org/datatype3.html
Specifically:
If the declared type contains the string "INT" then it is assigned INTEGER affinity.
(3) By David Raymond (dvdraymond) on 2022-11-09 18:08:58 in reply to 2 [link] [source]
But also things like collations on text columns aren't carried over either.
I have no strong opinion on whether those should be carried over for SQLite. But I do admit I've made a fair amount of use in Postgres of the
CREATE TABLE table_name (LIKE source_table);
style of create table statement.
(4) By Stephan Beal (stephan) on 2022-11-09 18:57:55 in reply to 3 [link] [source]
But also things like collations on text columns aren't carried over either.
They never are when you use the CREATE TABLE AS ... syntax. In order to get all of the finer details in your new table, you first have to create that able using a complete CREATE TABLE statement, then fill it using:
insert into target_table select * from source_table;
or similar.
(5.1) By frank10 on 2022-11-09 19:47:03 edited from 5.0 in reply to 2 [source]
Thank you. Yes, I read that.
But on a forum I got these responses:
there is never a good reason not to declare your integer primary key explicitely. Also note that the type INTEGER (note full spelling) is needed for this to work as intended and is a different beast than a column declared INT PRIMARY KEY.
and
You can remove the "without rowid" clause from the table definition; in this case, define an ID column explicitely this way: "ID integer primary key". That means copying the data to the new table. I insist on INTEGER here, INT wouldn't work as well.
I don't know why, but it seems INTEGER is internally bit different, even if it's automatically converted from INT...
Maybe the difference is only with PRIMARY because of the indexing and not for the other "normal" columns? And FK are instead similar to PK?
(6) By anonymous on 2022-11-09 20:04:03 in reply to 5.1 [link] [source]
There are indeed differences of INTEGER PRIMARY KEY (alias for ROWID), and a column which is an INT affinity, which is a PRIMARY KEY.
https://www.sqlite.org/autoinc.html
(7) By Ryan Smith (cuz) on 2022-11-10 11:42:15 in reply to 1 [link] [source]
What you show is not the steps for cloning a table - it resembles a possible step to create a temporary bag of bytes store for a table's data, but it will never function as the original table.
To be clear: "newT" here is merely a temporarily holding place for the data before it is (presumably) pushed into another version of the real table, perhaps with features changed from the original, as is typical to do in order to create some altered version of the original table that SQLite's ALTER TABLE mechanism does not support.
Steps for really cloning a table oldT into a new table "newT":
- Create a new empty table "newT" by the very same SQL which "oldT" was created from.
- Run SQL: INSERT INTO newT SELECT * FROM oldT;
So to answer the question: the cloning mechanism works perfectly well, preserving table columns/features EXACT as long as you use the correct method.
If by chance you do not have the original SQL that made the initial table, you can obtain it by:
SELECT sql FROM main.sqlite_master WHERE type='table' AND name='oldT';