Difference Between "Text" and "String" datatype in SQLite

The subtle thing to note here is that SQLite does not enforce the data type of values you put into columns. That means that you can put text into a numeric field, and so on.

To understand the difference between your two SQL statements, check out section 2.1 Determination Of Column Affinity, which maps the column types you provide to the storage classes SQLite uses.

In this case, the type string gets mapped to storage class NUMERIC via rule 5. Declaring the field as text in code would tell the DBMS to use the TEXT storage class. Again, since SQLite does not enforce the types of columns, your code will probably run fine when storing Strings as a NUMERIC column, as you note.

As an alternative example, you could define a column with type INTERESTING STUFF, and that would be mapped to the INTEGER storage class, via rule 1.

Overall, it's probably a good idea to just use text for your table definition.

This is an old question, but I want to highlight a specific difference between a STRING and TEXT column types. If a string looks like a numeric value, STRING will convert it into a numeric value, while TEXT will not perform any conversion.

e.g.

create table t1(myint INTEGER, mystring STRING, mytext TEXT);insert into t1 values ('0110', '0220', '0330');insert into t1 values ('-0110', '-0220', '-0330');insert into t1 values ('+0110', '+0220', '+0330');insert into t1 values ('x0110', 'x0220', 'x0330');insert into t1 values ('011.0', '022.0', '033.0');select * from t1

will output rows with values:

 myint mystring mytext   110      220   0330  -110     -220  -0330   110      220  +0330 x0110    x0220  x0330    11       22  033.0

This means leading zeros, zeros trailing decimal points, and plus symbol on the "numeric" values will be stripped. This will cause problems if you intend on doing a string match using the values read out of the table.