3 ms·

> all data is stored as a string no matter the table schema types specified The docs seem to suggest otherwise: > if a column is of type INTEGER and you try t

> all data is stored as a string no matter the table schema types specified The docs seem to suggest otherwise: > if a column is of type INTEGER and you try to insert a string into that column, SQLite will attempt to convert the string into an integer. If it can, it inserts the integer instead. From: https://www.sqlite.org/faq.html#q3 https://www.sqlite.org/faq.html#q3

tonyarkles 8y ago
>The docs seem to suggest otherwise: >> if a column is of type INTEGER and you try to insert a string into that column, SQLite will attempt to convert the string into an integer. If it can, it inserts the integer instead. There's nuance to your quote. From my recollection, this means that "321a" will be inserted as "321", but "foo" will be inserted as "foo" (into an INTEGER column). Definitely a wart, on an otherwise fantastic system.
- hellofunk 8y ago
  But “321” is still a string, not an integer.
- SQLite 8y ago
  Not quite right. The expression "CAST('321a' AS INTEGER)" will do as you suggest and ignore the trailing 'a' character, yielding an integer 123 result. But that only happens for an explicit CAST. Automatic type conversions must be reversible. That means that '321a' is inserted as a string in an INTEGER column, but '321' (without the trailing 'a') will be converted into an integer 123. PostgreSQL, MySQL, and SQL Server do exactly the same thing for the '321' case. For the '321a' case, the other three throw an error whereas SQLite just cancels the type conversion and inserts the original string.
  - tonyarkles 8y ago
    Ahhh cool! Either way, the fact that you can end up with strings in an Integer column is certainly surprising... sqlite> create table test (foo INTEGER); sqlite> insert into test (foo) values (123); sqlite> insert into test (foo) values ("blah"); sqlite> insert into test (foo) values ("123a"); sqlite> select * from test; 123 blah 123a
    - hellofunk 8y ago
      It’s not surprising if you read in the docs that it is an intended feature.

tonyarkles 8y ago

>The docs seem to suggest otherwise: >> if a column is of type INTEGER and you try to insert a string into that column, SQLite will attempt to convert the string into an integer. If it can, it inserts the integer instead. There's nuance to your quote. From my recollection, this means that "321a" will be inserted as "321", but "foo" will be inserted as "foo" (into an INTEGER column). Definitely a wart, on an otherwise fantastic system.

hellofunk 8y ago
But “321” is still a string, not an integer.
SQLite 8y ago
Not quite right. The expression "CAST('321a' AS INTEGER)" will do as you suggest and ignore the trailing 'a' character, yielding an integer 123 result. But that only happens for an explicit CAST. Automatic type conversions must be reversible. That means that '321a' is inserted as a string in an INTEGER column, but '321' (without the trailing 'a') will be converted into an integer 123. PostgreSQL, MySQL, and SQL Server do exactly the same thing for the '321' case. For the '321a' case, the other three throw an error whereas SQLite just cancels the type conversion and inserts the original string.
- tonyarkles 8y ago
  Ahhh cool! Either way, the fact that you can end up with strings in an Integer column is certainly surprising... sqlite> create table test (foo INTEGER); sqlite> insert into test (foo) values (123); sqlite> insert into test (foo) values ("blah"); sqlite> insert into test (foo) values ("123a"); sqlite> select * from test; 123 blah 123a
  - hellofunk 8y ago
    It’s not surprising if you read in the docs that it is an intended feature.