On Sat, Apr 04, 2009 at 02:22:33PM +1100, BareFeet scratched on the wall:
> Hi all,
>
> If I have a text column, insert text into it, the ask SQLite for the
> insert statement, shouldn't it show a text result? SQLite appears to
> coerce it to a number if it can, but shouldn't.
Asking SQLite for the insert statement doesn't tell you what the
type in the database is, it only produces a set of SQL statements
that will reproduce the correct results.
If the column is declared to have a TEXT affinity, any numbers will
be converted to text strings and stored as text. That's true of
your INSERT statements as well as the automatically generated
statements. The end result in both cases is numbers being stored as
strings.
The fact that the code that generates the INSERT statement decided to
represent the value as a number doesn't mean anything other than the
code is smart enough to know that it can coerce the number-as-text
to a number, since it will be coerced back into a text value upon INSERT.
> Here's an example:
>
> .mode insert
> create temp table "Test" (MyString text);
> insert into Test values (123);
> insert into Test values ('456');
> insert into Test values ('789x');
If you want to see what the actual types are in a column, use the
typeof( ) function. For example, after this
.mode list
select typeof(MyString), MyString from Test;
text|123
text|456
text|789x
As expected, everything is a text value.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Our opponent is an alien starship packed with atomic bombs. We have
a protractor." "I'll go home and see if I can scrounge up a ruler
and a piece of string." --from Anathem by Neal Stephenson
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
