2

I need to store Java's java.math.BigInteger numbers in SQL. I can safely assume that the numbers will be positive and less than 2^k for some k that will be known at table creation time. Lets say k = 256.

How do I store it in an RDBMS such that it allows me comparisons (<, >, = etc).

I am currently converting to byte array and storing them as VARBINARY(k+1).

I can also store them as VARCHAR if that helps. I need a DBMS independent way to do it.

EDIT1: following up to the comment by JBNizet below. I need to do the following types of SQL queries:

SELECT * FROM myTable WHERE bigInteger > 100
UPDATE myTable SET bigInteger = bigInteger + 10 WHERE bigInteger = 123

EDIT2: It is possible to compare VARCHAR with lexicographic ordering with some effort. However, the increment gives weird behavior given below (tested on SQLite):

CREATE TABLE T(Id INTEGER PRIMARY KEY, Name VARCHAR, test VARCHAR);
INSERT INTO T VALUES(1,'Tom', '000030000000000000000000000000');
SELECT * FROM T;

 1|Tom|000030000000000000000000000000

UPDATE T SET test = test+1;  
SELECT * FROM T;

 1|Tom|3.0e+25
Jus12
  • 17,824
  • 28
  • 99
  • 157
  • In your EDIT2: when you use a char value in an arithmetic expression like `test+1`, it gets implicitly converted to a numeric datatype, and then to go back in the table it's getting implicitly converted back to char. You're probably seeing roundoff from the roundtrip conversion. The exact behavior is going to be dependent on the specific DBMS you're running and what numeric types it supports. On SQLite, I'm guessing it got converted to a `REAL` to do the arithmetic. – Andrew Janke Nov 03 '14 at 04:30
  • (Also, if you're looking for portability, SQLite may not be the best test system. Its data model and SQL dialect differ from most of the other widely used RDBMSes.) – Andrew Janke Nov 03 '14 at 04:40

2 Answers2

3

The appropriate type is NUMERIC. However, the limitations on that type differ between different RDBMS. MySQL, for example, limits NUMERIC to 64 digits if I'm not mistaken, MS SQL limits to mere 38 digits, and PostgreSQL limits to 1000 digits.

You need to calculate how many decimal digits you'll need for the number. For example, 2256 is 1.15E77. This means you need 78 digits, so you'll declare your field NUMERIC(78). This will fail in MySQL and MS SQL, but will work fine on PostgreSQL.

There are database systems that use different names for this type, like DECIMAL (an alternative name in MySQL) or NUMBER (Oracle). Check the documentation of your particular RDBMS.

RealSkeptic
  • 33,993
  • 7
  • 53
  • 79
  • +1 but caveat: Looks like OP is using SQLite, which is an oddball; it stores integers as signed binary ints up to 8 bytes wide, and provides `NUMERIC` as a compatibility "affinity" layer that doesn't map directly to its actual storage type. It doesn't have normal decimal numeric types like most of the larger DBMSes. – Andrew Janke Nov 03 '14 at 04:35
  • SQLite just for testing. I using PostgreSQL, MySQL and h2 in the actual projects and I want to be able to switch between them. Therefore I need an RDBMS independent way. In the `UPDATE` example above, I will probably end up using transactions. – Jus12 Nov 03 '14 at 04:41
  • I doubt that you can do this in a completely database-agnostic way. One database will allow you to define your own datatype and overload the comparison and arithmetic operators for it, while others won't. – RealSkeptic Nov 03 '14 at 06:06
  • If the numbers are unsigned, I can do almost everything using lexicographic comparison. The only things I cannot do are `Sum, Avg` and a few other operations. – Jus12 Nov 04 '14 at 03:16
  • Indeed. And you won't find two RDBMS systems that will allow you to define that the same way. In PostgreSQL you won't be able to add two strings like the way you tried in SQLite, but you might be able to define a new type based on an existing type and overload the '+' and '/' operator for it. And for others, who knows. It's simply outside the scope of the SQL standard. – RealSkeptic Nov 04 '14 at 06:29
-1

If you have a constant precision you have to keep, then you could just peform integer arithmetic and store ints (as in mathematic integer, not java int).

Example: Required precision = 4 Input BigDecimal = new BigDecimal("12.3456")

Value stored to DB: 12.3456 * 10000 = 123456

Since it would still be a big int or smth like that, you could easily compare such numbers no matter what underlying RDBMS is.

Rafal G.
  • 4,252
  • 1
  • 25
  • 41
  • And how do you propose to store a 2^256 number in an integer (2^32) or bigint (2^64) datatype? – Mark Rotteveel Nov 02 '14 at 13:21
  • 1
    True, but he can store them as text, and would still be comparable using lexicographic comparison. – Rafal G. Nov 02 '14 at 13:25
  • 2
    @R4J no, it wouldn't unless they're all left-padded with 0 and have the same length. In lexicographic order, 200 is smaller than 3. BTW, the OP is asking about BigInteger, not BigDecimal. – JB Nizet Nov 02 '14 at 13:41
  • @JBNizet Yeah, I was too quick with reading and skipped important details. – Rafal G. Nov 02 '14 at 13:56
  • @JBNizet I was thinking of zero-padding and comparing them lexicographically. – Jus12 Nov 02 '14 at 16:28