SQLite Forum

The NUMERIC data type?

The NUMERIC data type?

(1) By anonymous on 2021-04-21 01:09:19 [link] [source]

What is the best (may be a dumb question) way to bind a column that is declared as an SQL Type - NUMERIC(10,2)

SQLite Doc says that with NUMERIC affinity may contain values using the 5 storage classes.

In the example SQL Type NUMERIC(10,2) the bind, I think that fits the SQL Type is sqlite3_bind_double(...), I have determined this by the fact that the type returned from the table is shown as NUMERIC(10,2), in this case it seem clear what the bind method is but, what if the return type is just NUMERIC, is the bind type then determined by the value that would be bound to the column rather than the description in the SQL Type from the table info.

I may have answered my own question but I don't know enough, at this time, about sqlite3 amalgamation and what could be used to determine bind type dynamically.

I hope I have explained my question well enough


(2.2) By Keith Medcalf (kmedcalf) on 2021-04-21 02:37:14 edited from 2.1 in reply to 1 [source]

All "values" in SQLite3 (that is, at the intersection of any row and column) may contain data of any of the following datatypes:

  • INTEGER, a 64-bit signed integer
  • REAL, a 64-bit IEEE-754 floating point number
  • TEXT, a bag-o-bytes that conforms to C String semantics containing UTF-8 or UTF-16 encoded data
  • BLOB, a bag-o-bytes that is nothing more than a bunch of bytes
  • NULL, a value that equals nothing else, not even itself, and is none of the above

There is an invisible line between your application and the SQLite3 library. You communicate across this line using the various sqlite3_bind* calls (send an application data item to SQLite3), sqlite3_column* (retrieve a column value from SQLite3 into your application), and for User Defined Functions you use the sqlite3_value* interfaces to retrieve parameter values into your application code, and sqlite3_result* interfaces to send data from your code back to the SQLite3 library.

For the column and value interfaces there is also the sqlite3_*_type API, which will allow you to query what datatype (from the aforementioned list) SQLite3 is using to store the particular value.

Each of these has a number of subinterface types _int, _int64, _double, _text, _text16, _blob, _blob64, _null depending on YOUR APPLICATION datatype (long, long long, double, char*, wchar*, unsigned char*, unsigned char* or nothing, that YOUR code is sending from or receiving into.

SQLite3 will automatically "convert" YOUR APPLICATION datatype to the internal type required. You can also use arbitrary datatypes such as FESTERING GIRAFFE POOP which will have its AFFINITY determined by scanning the specified string for clues (see the web page below).

A type of NUMERIC is not a datatype, NUMERIC is an affinity. It means either INTEGER or REAL as may be appropriate for the circumstances. SQLite3 also completely ignores anything you put in ornamentation such as brackets following the type name.

So the answer to your question has nothing whatsoever to do with SQLite3, but rather with your application. If the data is stored/to be stored into a double, then you would use the sqlite3_*_double interfaces. If the application variable was a 64-bit signed integer, then you would use the sqlite3_*_int64 interfaces. And so on and so forth.

See https://sqlite.org/datatype3.html for information on how data is handled once it crosses the invisible line between your application and SQLite3.

(3) By anonymous on 2021-04-21 07:52:01 in reply to 2.2 [link] [source]

Thank you for your response Keith,

In most part I understood all of that from reading the doco's.

I asked the question because I was unsure as to a proper approach to dealing with how MY COMPONENT handles a data type like FESTERING GIRAFFE POOP, your answer led me to an apparent solution (not yet tested) that allows for a property added to my custom edit controls that explicitly declares the bind type, i.e. Bind asDouble or Bind asText.

This approach would allow conversion in and out of, say, a text box on a form that shows a float value, in this case, the value would be converted to a float, formatted '####0.000" as a float string and the BiDi of the text box would be set right to left for incoming REAL/INTEGER values.

SQLite3 automatically converting data types to it's types is of no real concern, it's more to do with how MY COMPONENT's treat incoming and outgoing column values when generating the sql statements for insert's and update's.

Prior to my question, the component that generates the sql correctly constructed the statement including each parameter markers for each column prior to prepare and final binding based on data type.

Hope this makes sense?

(4.1) By Simon Slavin (slavin) on 2021-04-21 12:26:19 edited from 4.0 in reply to 1 [link] [source]

Because the documentation for different implementations of SQL argue over this, I'm going to start by defining what I think NUMERIC() means. The format is

NUMERIC(p,s) where
p == precision == the maximum number of decimal digits to store
s == scale == the number of those digits to the right of the decimal point

so the widest number which could be stored with NUMERIC(10,2) would be 12341234.12. Unlike the DECIMAL() type, maths on NUMERIC() values does not have to be perfectly respectful of decimal accuracy.

You can use NUMERIC. (You can include (p,s) at the end but it will be completely ignored.). It will do something useful. But you may want to avoid this so that values are never stored as strings.

The nearest types used by SQLite are REAL and INTEGER. For NUMERIC(p,s) where s = 0, I would use INTEGER. For others, I'd use REAL. Alternatively you might like to choose a type which fits the type of variable you're using in your programming language. So if, for example, you want to use C variables of type 'float' you would use REAL, even if you expect to store only integers.

(5) By Holger J (holgerj) on 2021-04-21 12:40:33 in reply to 4.1 [link] [source]

According to https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL (which is not itself the standard)

The types decimal and numeric are equivalent. Both types are part of the SQL standard.

It's really a pity that SQLite doesn't implement a true DECIMAL/NUMERIC data type, because all binary floating point types are not precise and therefore unusable for commercial calculations, and all integer type lack digits after the decimal point.

(6) By ddevienne on 2021-04-21 13:52:46 in reply to 5 [link] [source]

(7) By Warren Young (wyoung) on 2021-04-21 17:00:28 in reply to 5 [link] [source]

It's really a pity that SQLite doesn't implement a true DECIMAL/NUMERIC data type

Isn't that tantamount to lamenting the fact that SQLite isn't an arbitrary precision arithmetic engine? What's "Lite" about that?

If you want APA, strap one of the many available engines to SQLite, then store the results in SQLite columns. Depending on your needs, you might store the calculated data as:

  • strings to allow stringwize max() and such if you zero-pad the results; or
  • BLOBs containing some binary representation of the APA data structure; or
  • large integers, multiplied up to strip the fractional part (e.g. ×100 for US cents to dollars); or
  • multiple columns containing a serialized form of the APA data structure (e.g. two ints for mantissa and exponent)

binary floating point types are not precise and therefore unusable for commercial calculations

That depends on how it's done. If you use epsilon values carefully, you can keep things quite nicely on the rails using FP arithmetic.

Or, you can do everything with integer math (e.g. calculate on cents rather than dollars for US monetary calculations) and treat fractional dollars as a presentation issue.

(8) By Simon Slavin (slavin) on 2021-04-23 21:26:40 in reply to 5 [link] [source]

Dammit, I checked what I wrote twice because I didn't believe it. My sources said that DECIMAL() was for doing decimal maths and NUMERIC() could be used when decimal faithfulness wasn't needed. Now all I can find is sources which say that the two are identical, or can be considered to have the same characteristics.

Thanks for the correction.

(9) By Richard Damon (RichardDamon) on 2021-04-23 23:22:13 in reply to 8 [link] [source]

I think that some SQL systems might make DECIMAL work with decimal arithmetic rather than binary floating-point. SQLite isn't one of them.