Hi Jay,
Thanks for the reply.
> Asking SQLite for the insert statement doesn't tell you what the
> type in the database is
Shouldn't it? It doesn't seem that any output mode in the sqlite3
command will distinguish types. CSV sometimes quotes text, sometimes
doesn't. And now it seems insert doesn't quote text and not numbers.
> 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.
OK, let's change the test to not specify affinity for the column:
.mode insert
create temp table "Test" (MyString);
insert into Test values (123);
insert into Test values ('456');
insert into Test values ('789x');
select MyString from Test;
which should give:
insert into Test values (123);
insert into Test values ('456');
insert into Test values ('789x');
but instead gives:
INSERT INTO table VALUES(123);
INSERT INTO table VALUES(456);
INSERT INTO table VALUES('789x');
> 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.
In this case, that logic doesn't seem to hold. Reinjecting the
generated 456 insert statement above would insert an integer instead
of the original text. So the SQLite produced insert statement is
"wrong" in the sense that running it would give a different value
(type) than the original.
As I mentioned, there therefore doesn't appear to be any way to get
the command line to show the data in a table with implied type.
Thanks,
Tom
BareFeet
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
