How to insert a string in SQLite database without conversion ?

Question

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');

Answer 1

I'm not sure what you are trying, but essentially, it should be:

INSERT INTO configuration VALUES(42,'0042');

Answer 2

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.