storing big numbers into NUMERIC, DECIMAL columns

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
32 messages Options
12
Reply | Threaded
Open this post in threaded view
|

storing big numbers into NUMERIC, DECIMAL columns

LacaK
Hi,
I have table like this:
CREATE TABLE tab1 (
 a INTEGER,
 c DECIMAL(30,7),
 ...
);

When I am trying insert values like:
INSERT INTO tab1 (a,c) VALUES(1, 123456789123456789.123456);
INSERT INTO tab1 (a,c) VALUES(2, '123456789123456789.123456');

values for c column are always rounded or cast to :
1.23456789012346e+19

If I understand correctly column c has NUMERIC affinity, but when
storing values, they are stored using REAL storage class.
But why, when conversion from TEXT is NOT lossless ? (AFAIU first 15
significant digits are not preserved)
Is there way how to store numeric values, which are out of REAL range ?

TIA
-Laco.



_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: storing big numbers into NUMERIC, DECIMAL columns

Black, Michael (IS)
I hope you know what you're doing with trying to preserve that much significance.  Ths first time you stick it in a double or long double variable you'll lose it.  You can use the HPAlib to get 32 digits http://www.nongnu.org/hpalib/


// Example showing digit loss -- doesn't matter double or long double -- at least under GCC 4.1.2
#include <stdio.h>
int main()
{
        double d1=123456789123456789.123456;
        long double d2=123456789123456789.123456;
        printf("%f\n%Lf\n",d1,d2);
        return 0;
}
123456789123456784.000000
123456789123456784.000000

But since you don't seem to understand the limits of floating point values I'm worried you're heading down a failing path.

But if what you want to do will really work just make the field text and then do whatever you're doing that preserves the significant digits.  It's a common misconception that significant digits is to the right of the decimal point but that's not true.  It means ALL the digits.

sqlite> CREATE TABLE tab1 (
   ...>   a INTEGER,
   ...>   c DECIMAL(30,7),
   ...> d TEXT);
insert into tab1 values(1,123456789123456789.123456,'123456789123456789.123456');
1|123456789123456784|123456789123456789.123456


Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate



________________________________________
From: [hidden email] [[hidden email]] on behalf of LacaK [[hidden email]]
Sent: Tuesday, March 22, 2011 1:51 AM
To: [hidden email]
Subject: EXT :[sqlite] storing big numbers into NUMERIC, DECIMAL columns

Hi,
I have table like this:
CREATE TABLE tab1 (
  a INTEGER,
  c DECIMAL(30,7),
  ...
);

When I am trying insert values like:
INSERT INTO tab1 (a,c) VALUES(1, 123456789123456789.123456);
INSERT INTO tab1 (a,c) VALUES(2, '123456789123456789.123456');

values for c column are always rounded or cast to :
1.23456789012346e+19

If I understand correctly column c has NUMERIC affinity, but when
storing values, they are stored using REAL storage class.
But why, when conversion from TEXT is NOT lossless ? (AFAIU first 15
significant digits are not preserved)
Is there way how to store numeric values, which are out of REAL range ?

TIA
-Laco.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: storing big numbers into NUMERIC, DECIMAL columns

LacaK
In reply to this post by LacaK
Hi Michael,
thank you for response.
So only solution is use TEXT columns (with TEXT affinity) ? There is no
way how to use DECIMAL columns (with NUMERIC affinity) ?
My goal is store numeric values with big precision (as declared per
column DECIMAL(30,7)).
I do not want any conversion to floating-point values ... because such
conversion loses digits and is not reversible to original value.
What I will expect is: If supplied value can not be "reversibly"
converted to floating-point representation (REAL storage class), then
store it as text with TEXT storage class ... but this does not happen
(SQLite converts to floating-point and stores it and looses digits).

But on this page http://www.sqlite.org/datatype3.html is written:
"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. For conversions between TEXT
and REAL storage classes, SQLite considers the conversion to be lossless
and reversible if the first 15 significant decimal digits of the number
are preserved. *If the lossless conversion of TEXT to INTEGER or REAL is
not possible then the value is stored using the TEXT storage class*."

Laco.

> Hi,
> I have table like this:
> CREATE TABLE tab1 (
> a INTEGER,
> c DECIMAL(30,7),
> ...
> );
>
> When I am trying insert values like:
> INSERT INTO tab1 (a,c) VALUES(1, 123456789123456789.123456);
> INSERT INTO tab1 (a,c) VALUES(2, '123456789123456789.123456');
>
> values for c column are always rounded or cast to :
> 1.23456789012346e+19
>
> If I understand correctly column c has NUMERIC affinity, but when
> storing values, they are stored using REAL storage class.
> But why, when conversion from TEXT is NOT lossless ? (AFAIU first 15
> significant digits are not preserved)
> Is there way how to store numeric values, which are out of REAL range ?
>
> TIA
> -Laco.
>
>
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: storing big numbers into NUMERIC, DECIMAL columns

Igor Tandetnik
LacaK <[hidden email]> wrote:
>> So only solution is use TEXT columns (with TEXT affinity) ? There is no
>> way how to use DECIMAL columns (with NUMERIC affinity) ?
>> My goal is store numeric values with big precision (as declared per
>> column DECIMAL(30,7)).

SQLite happily ignores those numbers in parentheses. There is no arbitrary precision floating point data type in SQLite. You get to choose between text, 64-bit integers, 64-bit IEEE doubles, and blobs.

>> I do not want any conversion to floating-point values ... because such
>> conversion loses digits and is not reversible to original value.
>> What I will expect is: If supplied value can not be "reversibly"
>> converted to floating-point representation (REAL storage class), then
>> store it as text with TEXT storage class

Use affinity of NONE (don't specify any type), and figure out in your program for each value whether to store as a floating point number or as text. Use sqlite3_bind_double or sqlite3_bind_text accordingly.

>> But on this page http://www.sqlite.org/datatype3.html is written:
>> "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. For conversions between TEXT
>> and REAL storage classes, SQLite considers the conversion to be lossless
>> and reversible if the first 15 significant decimal digits of the number
>> are preserved. *If the lossless conversion of TEXT to INTEGER or REAL is
>> not possible then the value is stored using the TEXT storage class*."

In the examples you've shown, the first 15 significant digits are indeed preserved. SQLite appears to behave as documented. What again seems to be the problem?
--
Igor Tandetnik


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: storing big numbers into NUMERIC, DECIMAL columns

LacaK
In reply to this post by LacaK
>>/ So only solution is use TEXT columns (with TEXT affinity) ? There is no
/>>/ way how to use DECIMAL columns (with NUMERIC affinity) ?
/>>/ My goal is store numeric values with big precision (as declared per
/>>/ column DECIMAL(30,7)).
/
>SQLite happily ignores those numbers in parentheses. There is no arbitrary precision floating point data type in SQLite. You get to choose between text, 64-bit integers, 64-bit IEEE doubles, and blobs.

>>/ I do not want any conversion to floating-point values ... because such
/>>/ conversion loses digits and is not reversible to original value.
/>>/ What I will expect is: If supplied value can not be "reversibly"
/>>/ converted to floating-point representation (REAL storage class), then
/>>/ store it as text with TEXT storage class
/
>Use affinity of NONE (don't specify any type), and figure out in your program for each value whether to store as a floating point number or as text. Use sqlite3_bind_double or sqlite3_bind_text accordingly.

>>/ But on this page http://www.sqlite.org/datatype3.html is written:
/>>/ "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. For conversions between TEXT
/>>/ and REAL storage classes, SQLite considers the conversion to be lossless
/>>/ and reversible if the first 15 significant decimal digits of the number
/>>/ are preserved. *If the lossless conversion of TEXT to INTEGER or REAL is
/>>/ not possible then the value is stored using the TEXT storage class*."
/
>In the examples you've shown, the first 15 significant digits are indeed preserved. SQLite appears to behave as documented. What again seems to be the problem?

Yes you are right , it seemes so.
So once if I define column as DECIMAL,NUMERIC then there is no chance store in such column numeric values out of range of 64bit integers or 64bit floating point values, right ?
My guess, hope was, that if I use sqlite3_bind_text with for example '123456789123456789.12345' then sqlite3 stores such value as string and do not convert them to floating point.
(equal as when I insert non numeric value for example 'abcd' then 'abcd' is stored)
Thanks for your assistance
Laco.




_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: storing big numbers into NUMERIC, DECIMAL columns

Black, Michael (IS)
In reply to this post by LacaK
Hmmm...the docs do say that...but how do you get that value back out?  Retreiving it as text doesn't work.
You still don't say what you're planning on doing with these number...just displaying them?

I think the docs may be misleading...here is the comment in sqlite3.c
/*
** Try to convert a value into a numeric representation if we can
** do so without loss of information.  In other words, if the string
** looks like a number, convert it into a number.  If it does not
** look like a number, leave it alone.
*/

The "loss of information" simply means it still looks like a number...not that we lost any significant digits.  I think the docs should be clearer about that.
So storing them as text appears to be your only option.

The following just prints out the same truncated real number even though it's retrieved as text (and all by design).
You'll also find that a dump shows the same thing.
1.23456789123457e+17

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sys/types.h>
#include <sys/wait.h>
#include <unistd.h>
#include "sqlite3.h"
int
main (int argc, char *argv[])
{
  sqlite3 *db = NULL;
  int ret = -1;
  char *sql0 = "drop table tab1";
  char *sql1 = "create table tab1(a integer,c real)";
  char *sql2 = "insert into tab1 values(1,'123456789123456789.123456')";
  char *sql3 = "select * from tab1";
  char *errmsg;
  sqlite3_stmt *p_stmt;
  ret = sqlite3_open ("prec.db", &db);
  if (ret != SQLITE_OK) {
    fprintf (stderr, "open error\n");
    exit (-1);
  }
  sqlite3_exec (db, sql0, NULL, NULL, &errmsg);
  if (ret != SQLITE_OK) {
    fprintf (stderr, "exec error: %s\n", errmsg);
    exit (-1);
  }
  sqlite3_exec (db, sql1, NULL, NULL, &errmsg);
  if (ret != SQLITE_OK) {
    fprintf (stderr, "exec error: %s\n", errmsg);
    exit (-1);
  }
  sqlite3_exec (db, sql2, NULL, NULL, &errmsg);
  if (ret != SQLITE_OK) {
    fprintf (stderr, "exec error: %s\n", errmsg);
    exit (-1);
  }
  ret = sqlite3_prepare_v2 (db, sql3, -1, &p_stmt, NULL);
  if (ret != SQLITE_OK) {
    fprintf (stderr, "prepare error: %s\n", sqlite3_errmsg (db));
  }
  ret = sqlite3_step (p_stmt);
  if (ret == SQLITE_ROW) {
    const unsigned char *myval = sqlite3_column_text (p_stmt, 1);
    printf ("%s\n", myval);
  }
  sqlite3_finalize (p_stmt);
  sqlite3_close (db);
  return 0;
}


Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate



________________________________________
From: [hidden email] [[hidden email]] on behalf of LacaK [[hidden email]]
Sent: Tuesday, March 22, 2011 6:25 AM
To: [hidden email]
Subject: EXT :Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

Hi Michael,
thank you for response.
So only solution is use TEXT columns (with TEXT affinity) ? There is no
way how to use DECIMAL columns (with NUMERIC affinity) ?
My goal is store numeric values with big precision (as declared per
column DECIMAL(30,7)).
I do not want any conversion to floating-point values ... because such
conversion loses digits and is not reversible to original value.
What I will expect is: If supplied value can not be "reversibly"
converted to floating-point representation (REAL storage class), then
store it as text with TEXT storage class ... but this does not happen
(SQLite converts to floating-point and stores it and looses digits).

But on this page http://www.sqlite.org/datatype3.html is written:
"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. For conversions between TEXT
and REAL storage classes, SQLite considers the conversion to be lossless
and reversible if the first 15 significant decimal digits of the number
are preserved. *If the lossless conversion of TEXT to INTEGER or REAL is
not possible then the value is stored using the TEXT storage class*."

Laco.

> Hi,
> I have table like this:
> CREATE TABLE tab1 (
> a INTEGER,
> c DECIMAL(30,7),
> ...
> );
>
> When I am trying insert values like:
> INSERT INTO tab1 (a,c) VALUES(1, 123456789123456789.123456);
> INSERT INTO tab1 (a,c) VALUES(2, '123456789123456789.123456');
>
> values for c column are always rounded or cast to :
> 1.23456789012346e+19
>
> If I understand correctly column c has NUMERIC affinity, but when
> storing values, they are stored using REAL storage class.
> But why, when conversion from TEXT is NOT lossless ? (AFAIU first 15
> significant digits are not preserved)
> Is there way how to store numeric values, which are out of REAL range ?
>
> TIA
> -Laco.
>
>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: storing big numbers into NUMERIC, DECIMAL columns

Igor Tandetnik
In reply to this post by LacaK
LacaK <[hidden email]> wrote:
>> So once if I define column as DECIMAL,NUMERIC then there is no chance store in such column numeric values out of range of 64bit
>> integers or 64bit floating point values, right ?

Well, no chance to store them losslessly, preserving the precision.

Where does this precision come from in the first place? How do you represent these numbers in your program? Where do they come from? It's highly unlikely that you can measure any real-world signal this accurately.
--
Igor Tandetnik


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: storing big numbers into NUMERIC, DECIMAL columns

LacaK
In reply to this post by LacaK
> You still don't say what you're planning on doing with these number...just displaying them?

Yes may be ...
I am working on modification of database component for accessing SQLite3 databases for FreePascal project.
We map declared column's types to native freepascal internal field types.
So DECIMAL and NUMERIC is mapped to TFmtBCDFieldType (which is able to hold up to 64 digits)
We read column value using sqlite3_column_text and then convert string representation into TBCD (which is internal structure for "arbitrary" precision numbers)
But problem arrives when we want write back values.
We use sqlite3_bind_text and as I wrote a this point we loose precision (because SQLite3 forces conversion to floating point values).

TIA
-Laco.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: storing big numbers into NUMERIC, DECIMAL columns

TR Shaw

On Mar 22, 2011, at 9:12 AM, LacaK wrote:

>> You still don't say what you're planning on doing with these number...just displaying them?
>
> Yes may be ...
> I am working on modification of database component for accessing SQLite3 databases for FreePascal project.
> We map declared column's types to native freepascal internal field types.
> So DECIMAL and NUMERIC is mapped to TFmtBCDFieldType (which is able to hold up to 64 digits)
> We read column value using sqlite3_column_text and then convert string representation into TBCD (which is internal structure for "arbitrary" precision numbers)
> But problem arrives when we want write back values.
> We use sqlite3_bind_text and as I wrote a this point we loose precision (because SQLite3 forces conversion to floating point values).

Laco

If you already have an arbitrary precision number, just encode it to text, save it in sqlite and then decode on the way out.

Tom
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: storing big numbers into NUMERIC, DECIMAL columns

LacaK
In reply to this post by LacaK
> If you already have an arbitrary precision number, just encode it to
> text, save it in sqlite and then decode on the way out.

Yes it is possible, but such values (and databases) will not be readable
by other database connectors
(like for example in PHP etc.)
Problem will be solved if SQLite will store such values as text ... so
will behave like this:
1. is supplied value in TEXT (sqlite3_bind_text)
2. if yes then try convert this text value into INTEGER or REAL
3. convert back to text and compare with original value
4. if equal then store it as INTEGER or REAL, if not then store it as is
as supplied in (1).

Laco.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: storing big numbers into NUMERIC, DECIMAL columns

Igor Tandetnik
LacaK <[hidden email]> wrote:
>> Problem will be solved if SQLite will store such values as text ... so
>> will behave like this:
>> 1. is supplied value in TEXT (sqlite3_bind_text)
>> 2. if yes then try convert this text value into INTEGER or REAL
>> 3. convert back to text and compare with original value
>> 4. if equal then store it as INTEGER or REAL, if not then store it as is
>> as supplied in (1).

Does this mean that, say, '042' or '42.00' are stored as text? Do you think that would be desirable?
--
Igor Tandetnik


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: storing big numbers into NUMERIC, DECIMAL columns

LacaK
In reply to this post by LacaK
>>/ Problem will be solved if SQLite will store such values as text ... so
/>>/ will behave like this:
/>>/ 1. is supplied value in TEXT (sqlite3_bind_text)
/>>/ 2. if yes then try convert this text value into INTEGER or REAL
/>>/ 3. convert back to text and compare with original value
/>>/ 4. if equal then store it as INTEGER or REAL, if not then store it as is
/>>/ as supplied in (1).
/
> Does this mean that, say, '042' or '42.00' are stored as text?

no

> Do you think that would be desirable?

no, of course

Base idea is store as TEXT when :
1. column value is supplied as TEXT (only in case sqlite3_bind_text)
2. conversion to REAL or INTEGER leads to loose of precision (digits)

I do not know details how to implement it ;-)
May be,
1. strip out leading and trailing spaces and zeroes
2. and then analyze string if contains only digits and decimal separator
3. count number of digits and if > than max precision for REAL or INTEGER then do not convert, but store as is

-Laco.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: storing big numbers into NUMERIC, DECIMAL columns

Igor Tandetnik
On 3/22/2011 9:53 AM, LacaK wrote:
> Base idea is store as TEXT when :
> 1. column value is supplied as TEXT (only in case sqlite3_bind_text)
> 2. conversion to REAL or INTEGER leads to loose of precision (digits)
>
> I do not know details how to implement it ;-)
> May be, 1. strip out leading and trailing spaces and zeroes 2. and then
> analyze string if contains only digits and decimal separator

Which of the following should be left as text:

'1e+003'
'10e2'
'1000'
'+.01e05'

--
Igor Tandetnik

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: storing big numbers into NUMERIC, DECIMAL columns

Konrad Hambrick
In reply to this post by LacaK


LacaK wrote, On 03/22/2011 08:53 AM:
>>> / Problem will be solved if SQLite will store such values as text ... so


Laco --

Problem will be solved when you teach SQLite to store such values as text.

This library might help your project:

http://speleotrove.com/decimal/

-- kjh
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: storing big numbers into NUMERIC, DECIMAL columns

Jonathan Allin
Igor,

 

Is there another way of looking at the problem by considering how Java and
other libraries handle big integers and big decimals?

 

Can you store the numeric value across sufficient cells necessary to achieve
the required precision?

 

¬Jonathan

 

From: [hidden email]
[mailto:[hidden email]] On Behalf Of Konrad J Hambrick
Sent: 22 March 2011 15:25
To: General Discussion of SQLite Database
Subject: Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

 

 

LacaK wrote, On 03/22/2011 08:53 AM:
>>> / Problem will be solved if SQLite will store such values as text ... so


Laco --

Problem will be solved when you teach SQLite to store such values as text.

This library might help your project:

http://speleotrove.com/decimal/

-- kjh
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

  _____  

No virus found in this message.
Checked by AVG - www.avg.com
Version: 10.0.1204 / Virus Database: 1498/3521 - Release Date: 03/21/11

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: storing big numbers into NUMERIC, DECIMAL columns

Igor Tandetnik
On 3/22/2011 12:50 PM, Jonathan Allin wrote:
> Igor,
>
> Is there another way of looking at the problem by considering how Java and
> other libraries handle big integers and big decimals?

They have data types for them, and the library to support them.

> Can you store the numeric value across sufficient cells necessary to achieve
> the required precision?

Who is "you" in this picture? If you mean "SQL engine", there are plenty
that can do this, but SQLite is not one of them (hence "lite"). If you
mean "application programmer", then sure, you can invent some
representation for your big numbers (or use a library that provides
one), and store them in the database as text or blobs.
--
Igor Tandetnik

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: storing big numbers into NUMERIC, DECIMAL columns

Simon Slavin-3
In reply to this post by LacaK

On 22 Mar 2011, at 1:12pm, LacaK wrote:

>> You still don't say what you're planning on doing with these number...just displaying them?
>
> Yes may be ...
> I am working on modification of database component for accessing SQLite3 databases for FreePascal project.
> We map declared column's types to native freepascal internal field types.

Here are two options which will let you get the contents back to the original precision:

A) Store the values as BLOBs.
B) Store the value as TEXT, but add a non-digit to the beginning of each number value, for example

X24395734857634756.92384729847239842398423964294298473927

Both methods will prevent SQLite from trying to see the value as a number.  Oh and since nobody seems to have pointed it out yet, SQLite doesn't have a NUMERIC or a DECIMAL column type.  The types can be found here:

http://www.sqlite.org/datatype3.html

Putting INTEGER and REAL together gives you NUMERIC, but there's no way to declare a column of that type, just a value.  The page actually rehearses your problem, showing when strings containing numeric values can be converted to a number.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: storing big numbers into NUMERIC, DECIMAL columns

Nico Williams
In reply to this post by LacaK
You can store any big-number representation you like as TEXT or BLOB
values.  The main issue is that you'll lose syntactic sugar: SQLite3
won't be able to treat those as numeric values, therefore it won't be
able to compare numerically nor use arithmetic with such values.  You
can get some of that back with user-defined functions, but not
automatic conversions.  Just pick a decent bignum library,
canonicalize bignums before binding such values to any statements, and
add user-defined functions and collations via which to invoke the
bignum library from SQL.

To do better than this would probably require significant surgery on
SQLite3.  (Though it might not be a bad idea anyways, but who would do
it?)

If you can manage to live with integers and use those to represent
floating point values, then that's by far your best option.  (The
typical example on this list is money.  For something like U.S.
dollars you'd store numbers as integer counts of tenths of a cent, so
that $2.599 becomes 2599, allowing you to count over $9,000 trillion,
which will be enough for a while, but is already on the low side.)
For scientific, mathematical, or other purposes where you really need
huge numbers, you may want to pursue the bignum shoehorn approach.

Nico
--
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: storing big numbers into NUMERIC, DECIMAL columns

LacaK
In reply to this post by LacaK
> Here are two options which will let you get the contents back to the original precision:

> A) Store the values as BLOBs.
> B) Store the value as TEXT, but add a non-digit to the beginning of each number value, for example

> X24395734857634756.92384729847239842398423964294298473927

> Both methods will prevent SQLite from trying to see the value as a number.  Oh and since nobody seems to have pointed it out yet, SQLite doesn't have a NUMERIC or a DECIMAL column type.  The types can be found here:

 <http://www.sqlite.org/datatype3.html>
Hi all,
thank you all for your answers, advices.

So conclusion is:
A) use sqlite3_bind_blob() then no conversion is performed
B) use sqlite3_bind_text() but with some hack, which "invalidates" numbers

-Laco.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: storing big numbers into NUMERIC, DECIMAL columns

Black, Michael (IS)
In reply to this post by LacaK
Blob may be better if you need speed -- then no conversion is necessary inside your Pascal code to/from a string.
But if you want to be able to see and understand your database text is better (or you have to write a special Pascal program to decode your database to look at any problems).

And...no conversion is performed if you declare the field as text and insert as text.

sqlite> create table tab1 (a int,c text);
sqlite> insert into tab1 values (1,'24395734857634756.92384729847239842398423964294298473927');
sqlite> select * from tab1;
1|24395734857634756.92384729847239842398423964294298473927


Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate



________________________________________
From: [hidden email] [[hidden email]] on behalf of LacaK [[hidden email]]
Sent: Wednesday, March 23, 2011 2:20 AM
To: [hidden email]
Subject: EXT :Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

> Here are two options which will let you get the contents back to the original precision:

> A) Store the values as BLOBs.
> B) Store the value as TEXT, but add a non-digit to the beginning of each number value, for example

> X24395734857634756.92384729847239842398423964294298473927

> Both methods will prevent SQLite from trying to see the value as a number.  Oh and since nobody seems to have pointed it out yet, SQLite doesn't have a NUMERIC or a DECIMAL column type.  The types can be found here:

 <http://www.sqlite.org/datatype3.html>
Hi all,
thank you all for your answers, advices.

So conclusion is:
A) use sqlite3_bind_blob() then no conversion is performed
B) use sqlite3_bind_text() but with some hack, which "invalidates" numbers

-Laco.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
12