Storing uint64 values in DB

[Tong Sun]

Neither PostgreSQL[1] nor SQLite[2] support unsigned 64-bit integers at the SQL level. 

However, I do need to save those unsigned 64-bit integers as-is into database, as they are not numbers but hash values. 

What's the proper way to cast my uint64 as database signed integer and back safely in Go? 

Let's use 8-bit variable as example, in C, I just assign the unsigned 8-bit variable to a signed 8-bit variable, so 255 will "looks like" -1 when in latter form. However, no info is lost, and when I assign it back from signed 8-bit variable, I'm still getting 255. How to do the same thing in Go? 

Thanks

[1] https://stackoverflow.com/questions/21909027/postgres-data-types-how-to-store-uint64-in-postresql

[2] https://sqlite.org/datatype3.html

[Andy Balholm]

var i uint64

var j int64

…

j = int64(i)

This performs the same conversion that C would do automatically when assigning i to j, but Go requires you to be a little more explicit.

--
You received this message because you are subscribed to the Google Groups "golang-nuts" group.
To unsubscribe from this group and stop receiving emails from it, send an email to golang-nuts...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Jesper Louis Andersen]

An alternative is just to use a 'bytea' or a bitstring in Postgres since you have a hash value anyhow.

* It'll take 9 bytes rather than 8. But space is usually cheap nowadays.

* a bitstring will take exactly 8 bytes.

* a bytea is varying and can be extended to a larger hash later easily if needed.

* You can get a hex representation directly in the psql(1) console.

* The data becomes more agnostic: you don't need to know the int->uint translation is needed and other systems will have an easier time interoperating.

* You won't accidentally do integer arithmetic on the hash value.

Of course, the overhead should be measured, but I don't think it'll be that big in practice. The major drawback is when doing table scanning where more bytes are stuffed in there. If the system isn't efficiency-critical I'd strongly recommend storing data in a representation which match the actual data you are working with.