DECIMAL with full precision SUM on QuestDB

Question

I have some finance numbers (as in 1234567891234567891234.1234567891234567) where I need to have full precision, and I cannot find how to achieve this on QuestDB, as both float and double would not be suitable.

At the proposal of a core engineer at QuestDB slack I am storing now the integer part and decimal part as two different long numbers. But I cannot figure out how to use them for aggregations.

CREATE TABLE test_sum (
    name SYMBOL,
    ts timestamp,
    price_integer_part long,
    price_decimal_part long
) TIMESTAMP(ts) PARTITION BY DAY WAL;

-- 10000.9998 + 10000.9998 = 20001.9996
INSERT INTO test_sum VALUES ('btc', '2023-07-14', '10000','9998');
INSERT INTO test_sum VALUES ('btc', '2023-07-14', '10000','9998');

Answer 1

After some fiddling, this works, but you need to know the number of decimal places. The result of the operation is in the last column, but I am leaving the rest of the columns just to see what we are calculating step by step.

SELECT
    name,
    SUM(price_integer_part) + SUM(price_decimal_part) / 10000 AS total_price,
    FLOOR(SUM(price_decimal_part) / 10000) AS carry,
    SUM(price_decimal_part) % 10000 / 10000 AS new_decimal,
    SUM(price_decimal_part) % 10000::double / 10000 AS new_decimal_fraction,
    (SUM(price_integer_part) + SUM(price_decimal_part) / 10000) + (SUM(price_decimal_part) % 10000::double / 10000) as result
FROM test_sum;

It gives the exact result of 20001.9996.

The values of the intermediate columns are: total_price:20001, carry:1, new_decimal:0, new_decimal_fraction:0.9996