1

I have to manage RFID tags in java storing them into Postgresql and I'm wondering what could be the best way to handle big numeric data.

My tags are 8bytes wide and I have no maths to do with them, just indexing and confronting.

Those IDs are used in a JavaFX app as authentication method and pushed into a remote PostgreSQL via JSON. The RFID itself is used as a key in some tables (mostly logs and tags management).

I'm using json-simple-1.1 and as far as I know (and correct me if I'm wrong) the only numeric type available is long so - again - there would be a lot of bignumeric to string (and viceversa) conversions.

I found these possibilities:

  1. numeric(20,0) with 20 length and 0 precision coupled with BigInteger
  2. character(16) [hex representation of 8byte integer] maybe vachar is better?
  3. bytea

The latter is probably the worst. Since I need to display the code in many interfaces maybe string format is better (it saves me the effort of conversion) but I'm worried about the perfomance drop having to use a textual primary key in a large table...

Any suggestion?

Azathoth
  • 582
  • 1
  • 7
  • 29
  • Postgresql BigInt is signed. As for bytea I misunderstood the docs (http://www.postgresql.org/docs/9.3/static/datatype-binary.html#AEN5376), you're right there is no need of useing 2 columns – Azathoth Mar 14 '16 at 15:02
  • The easiest way is to use simple `numeric` column without any restrictions. Most efficient way - `bigint` column. – Ihor Romanchenko Mar 14 '16 at 15:10
  • Speaking of decimal I need 20 digits, max signed value representable with 8 bytes is 0x8000 0000 0000 0000 (or maybe 0x7FFF FFFF FFFF FFFF) all rfid larger than that will be represented as negative. I will get an overflow converting someting bigger – Azathoth Mar 14 '16 at 15:10
  • Character representation of 8 byte in hex... – Azathoth Mar 14 '16 at 15:33
  • @IgorRomanchenko How do you think is possible to use bigint? – Azathoth Mar 14 '16 at 15:53
  • The same way you use `long` in java. It is perfectly fine to store some of the values as negatives as long as you do not do match in them. – Ihor Romanchenko Mar 14 '16 at 16:03
  • BTW You may want to read why java supports only signed primitive types:http://stackoverflow.com/questions/430346/why-doesnt-java-support-unsigned-ints – Ihor Romanchenko Mar 14 '16 at 16:04
  • @IgorRomanchenko Some math effort is needed in order to elaborate the tags to avoid overflow while saving into db. I think is simpler (as you said) to use numeric & BigInteger. – Azathoth Mar 14 '16 at 16:54

1 Answers1

2

Use the bigint, just shift values so that they fit in the signed format instead of unsigned. If it was 1 byte you'd subtract 128 from every value before saving it in the database. Of course you have to take this into account when searching or otherwise moving data from database back into your application.

meh
  • 63
  • 6
  • Quite a nice solution! But I think numeric & BigInteger is a better solution. I dont think that the performance save will cover the pain of restoring the original value every time is needed. If not re-converted my stored "unsiged-padded-to-signed" value will never match any other commercial RFID reader readings. Thanks for your effort. – Azathoth Mar 14 '16 at 17:04