Binary Transfer for Numeric Data Type

[samuel-massinon]

After trying to get it to work for this PR #223 I thought it would be best to do it in a future PR.

The main issue with numeric data is the it's binary format is somewhat complex and quite a bit of effort to implement correctly and finding all the edge cases to test. But, I did some work to make the implementation happen and will add here my work.

I had a very hard time finding info explaining this. I really just looked at open source postgres code and some random forums that talked about it (see links below).

Let's use 12.34567 as an example. It looks like this with bytes when trasnfering in binary

numeric_bytes = UInt8[0x00, 0x03, 0x00, 0x00, 0x00, 0x00, 0x00, 0x05, 0x00, 0x0c, 0x0d, 0x80, 0x1b, 0x58, 0x00]

(note that each line is a UInt8)

The first two bytes represent the number of Int16s passed that represents the digits.

julia> number_of_digits = ntoh(reinterpret(UInt16, numeric_bytes[1:2])[1])
0x0003

The next two bytes represent the weight of the numeric value. I am not sure what this is used for. I think it might be 10^weight.

julia> weight = ntoh(reinterpret(Int16, numeric_bytes[3:4])[1])
0

The next two bytes are for the sign. This value can represent not just positive or negative, but also NaN and (+/-)infinite. There are constants to figure out what value means what.

julia> sign = ntoh(reinterpret(UInt16, numeric_bytes[5:6])[1])
0x0000

The next two bytes are the decimal scale. This represents where the decimal is placed in the digits we get later. I believe we get 5 because there are 5 digits have the decimal in 12.34567.

julia> decimal_scale = ntoh(reinterpret(UInt16, numeric_bytes[7:8])[1])
0x0005

Now, we use the number_of_digits above to get the next number_of_digits Int16. That will represent the digits.

julia> first_digits = ntoh(reinterpret(Int16, numeric_bytes[9:10])[1])
12

julia> second_digits = ntoh(reinterpret(Int16, numeric_bytes[11:12])[1])
3456

julia> third_digits = ntoh(reinterpret(Int16, numeric_bytes[13:14])[1])
7000

With those digits we get 1234567000. We cut off the last 0s to get 1234567. With the decimal_scale of 5, we add the decimal 5 from the left, so 12.34567

Here's some links to sources that kinda explain how I figured this out

• Postgres numeric_send function
• Postgres numeric_recv function
• An explanation of what each of the bytes represent