The table: CREATE TABLE configuration(Key STRING, Value STRING, PRIMARY KEY (Key) );
Here is what I tried: insert into configuration(Key,Value) values(42,cast('0042' as text));
Here is the dump: INSERT INTO "configuration" VALUES(42,42);
What I wanted: INSERT INTO "configuration" VALUES(42,'0042');
If you create the table with:
CREATE TABLE configuration(Key STRING, Value TEXT, PRIMARY KEY (Key) );
(there is no storage penalty for doing this with SQLite) then you'll get the leading zeroes preserved, even if you use the very simplest form of INSERT. This is because STRING is not a real SQLite type, and so has NUMERIC affinity.
STRING will be interpreted to TEXT. So would VARCHAR, VARCHAR2, and POTATO. (I kid you not).
TEXT it works and when I use STRING it doesn't. I simply tested it.I'm not sure what you are trying, but essentially, it should be:
INSERT INTO configuration VALUES(42,'0042');