You are getting exactly what is documented and exactly what you asked for. Declaring a column NUMERIC means you intend to store NUMBERS. Leading zeros do not change the value of a number. 0012 == 12 unless you have a convention of interpreting a leading zero as indicating octal base.
If you need to display numbers zero filled on the left, that would be the task of the presentation layer. See also the printf() function. Lossless and reversible means 15 significant digits. Leading zeros are NOT significant. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Shawn Wagner Gesendet: Sonntag, 13. Oktober 2019 23:12 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: [EXTERNAL] [sqlite] Possible bug in storing text values in numeric columns The documentation for a column with NUMERIC affinity says > When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if such conversion is lossless and reversible. But consider: sqlite> create table foo(bar numeric); sqlite> insert into foo values ('0012'); sqlite> select bar, typeof(bar) from foo;bar typeof(bar) ---------- -----------12 integer As you can see, the leading zeros in the original string are gone and it's been converted to an integer. This seems to violate the "lossless and reversible" constraint. Shouldn't it be kept as text? _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
