SQLite – Update Table Schema with Correct Datatype

sqlite

I know that importing from CSV into SQLite treats all fields as text. According to How to import csv file to sqlite with correct data types, one can use 

UPDATE MyTable SET MyColumn = CAST(MyColumn AS INTEGER)

to update whole colum type. That seems to work, however that doesn't seem to be reflected in the output of TYPEOF() nor in PRAGMA table_info(table_name)

sqlite> CREATE TABLE testtb (a TEXT, b TEXT);
sqlite> INSERT INTO testtb VALUES ('1', '2');
sqlite> UPDATE testtb SET b = CAST(b AS INTEGER);
sqlite> SELECT TYPEOF(b) FROM testtb;
text
sqlite> PRAGMA table_info(testtb);
0|a|TEXT|0||0
1|b|TEXT|0||0

So how can I make the changes actually show up when querying table's meta information (I'm lacking a proper phrasing here, but hopefully this makes sense) ?

Best Answer

If you want really want to, just use your first table as a staging table before you go ahead and create the final table with correct datatypes & data:

sqlite> PRAGMA table_info(testtb);
0|a|TEXT|0||0
1|b|TEXT|0||0
sqlite> create table testtb2 (a INT, b INT);
sqlite> insert into testtb2 (a,b) select a,b from testtb ;
sqlite> select * from testtb2;
1|2
sqlite> PRAGMA table_info(testtb2);
0|a|INT|0||0
1|b|INT|0||0
sqlite>

At the end of the day you have to remember (to some extent) that sqlite doesn't actually care about datatypes.

SQLite is very flexible with regard to datatypes.

Some commentators say that SQLite is "weakly typed" and that other SQL databases are "strongly typed". We consider these terms to be inaccurate and purgative. We prefer to say that SQLite is "flexibly typed" and that other SQL databases are "rigidly typed".

The key point is that SQLite is very forgiving of the type of data that you put into the database. For example, if a column has a datatype of "INTEGER" and the application inserts a text string into that column, SQLite will first try to convert the text string into an integer, just like every other SQL database engine. Thus, if one inserts '1234' into an INTEGER column, that value is converted into an integer 1234 and stored. But, if you insert a non-numeric string like 'wxyz' into an INTEGER column, unlike other SQL databases, SQLite does not throw an error. Instead, SQLite stores the actual string value in the column.

Related Question