Drop or convert copied value when copying row to another table-CodePudding

I'm migrating a database. One of my columns types is changing from TEXT to an INTEGER.

My strategy is to rename the old table and create a new one, then insert the rows of the old table into the new one.

Supposing my new table is something like this:

CREATE TABLE Item (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    count INTEGER NOT NULL DEFAULT '0'
)

My code for transferring the data might look like this:

INSERT INTO Item (id, count)
    SELECT id, count FROM old_Item

I originally defined the count as TEXT when I probably should have defined it as INTEGER. Due to dynamic typing, I would expect this to be fine so long as the count entries are parsable as integers, but I cannot guarantee that something other than an Integer has ever been entered for the count column without spending a lot of time reviewing many older versions of the app going back ten years.

Is there a way to cause this column to be replaced with some default integer value when the column from the old table has an invalid TEXT value?

I know that NULL values can be automatically replaced by default values if I use ON CONFLICT REPLACE (or is it UPSERT ON CONFLICT REPLACE?) in my statement, but I don't know how this would affect mismatched types.

Or do I need to do things the hard way and instead of using SELECT, query the old table and manually parse every row of the returned Cursor in Java/Kotlin and then insert them row by row?

CodePudding user response：

You can use CAST function

SELECT id, CAST(count AS int) FROM old_Item

If value is text, the result of CAST will be 0. If value is Real it will be converted as integer

For Example

INSERT INTO old_Item (count) values ('2');
INSERT INTO old_Item (count) values ('5.03');
INSERT INTO old_Item (count) values ('tXt22');
INSERT INTO old_Item (count) values (' 5.8');
INSERT INTO old_Item (count) values ('0x533');

Result of SELECT CAST(count as INT) from old_Item will be

So, in your case you can

Make a query

INSERT INTO Item SELECT id, CAST(count as int) FROM old_Item

Values will be 0 if it cannot be recognized as int. If you want to replace 0 as some value, you need to make this query

select id, 
(case when count_int = 0 then your_default_value else count_int end) 
from (select id, cast(count as integer) count_int from old_Item);

Full explanation how CAST function works in sqllite you can find here

https://sqlite.org/lang_expr.html#castexpr

Text in Conversion Processing of type-name - INTEGER