Type issue on select with digits only in a "string" colums. about rusqlite HOT 2 CLOSED

Not related to rusqlite:
You should try this on https://sqlite.org/fiddle/index.html:

sqlite> CREATE TABLE producer (producer_id    integer, producer_label    string NOT NULL, PRIMARY KEY(producer_id));
sqlite> INSERT INTO producer VALUES (1, 'Lisa'), (2, '1234');
sqlite> SELECT *, typeof(producer_label) FROM producer;
1|Lisa|text
2|1234|integer

To understand why the last row has an integer type,
First read typeof documentation: https://sqlite.org/lang_corefunc.html#typeof
Then you should read this page:
https://sqlite.org/datatype3.html#determination_of_column_affinity
where you can find that currently, string type has affinity NUMERIC:

If the declared type of the column contains any of the strings "CHAR", "CLOB", or "TEXT" then that column has TEXT affinity. Notice that the type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity.
Otherwise, the affinity is NUMERIC.

So you must fix the column type here:

producer_label	string NOT NULL,

You may also use STRICT mode for your table if you are not comfortable with SQLite dynamic typing.

from rusqlite.

Thanks for this very detailed answer ! and for the documentation pointers.
It is much more clear to me.
I will fix my DB definition.
Thanks.

from rusqlite.