If I try to insert data with a 20 digit primary key into a SQLite database I get an error with the second insert statement because it "is not unique". If I select the values I can see from the SQLite commandprompt that the primary key is written in scientific notation. The column type is decimal. Is there a way to force SQLite to insert the values "as they are" keeping the precision / "normal representation" even with values that long?
Well, I had to define the column as TEXT and insert the values quoted. I think this is because of SQLite's type-affinity
tobsen
2008-10-14 21:25:40
Steve, that is *so* cold. I have to remember that one.
Anders Eurenius
2008-10-14 21:33:32
+2
A:
the primary key field must be an integer, the largest value is 9223372036854775807. use a separate (indexed) field, and insert the value as string.
Javier
2008-10-14 20:53:17
There is no requirement as to the primary key being an integer in SQLite. The rowid is a 64-bit integer, but that's a different matter altogether. The primary key can be any data type you like. See here for details: http://www.sqlite.org/autoinc.html
Mihai Limbășan
2008-10-14 20:58:48
A:
Strange, sounds like your wrapper is putting the number in as a float. I thought DECIMAL was mapped to an integer internally. Using a string would work but may be slow do to the indexing of the string rather than an int.
DECIMAL has numeric affinity. This means that any data type can be stored, but strings are converted to INTEGER or REAL if possible. The OP probably declared an `INTEGER PRIMARY KEY` column, which *does* require a 64-bit signed integer.
dan04
2010-07-31 19:34:20