Postgres queries that return values of type bigint, decimal, and numeric are being treated as strings by Retool. I understand the technical reasons for this: Javascript cannot represent the largest bigint values or the precision of decimal. However, I believe that for 99.99% of users and use cases, it would be better to convert the value to a Javascript number and warn if the value was truncated.
select count(*), avg(tally) from records;
The count() aggregate function returns bigint, and avg() returns numeric for integer arguments. If you try to display these using the Statistic component it shows an error, because the input is a string, not a number. I can manually cast these to other types (integer or double precision) either in the query or using parseFloat, but that is rather cumbersome.
Hey @trevorlynnsmith, and welcome to the community! Apologies for the delay, the Retool team has been enjoying a few days off
You are correct! Retool does coerce numbers to strings in a lot of cases like these. I think you definitely make a good point and I'll share it with the rest of the team!
Following up with some more info — when we serialize bigints, numeric, and decimal data types to JSON, we cast them to strings to avoid loss in precision.
We do want to be careful that we don’t break existing apps that might rely on the current behavior. I think that adding an option to parse all number types into JS Numbers is a reasonable path forward to maintain back-compat, but we’ll need to do a little thinking on where to put this option.
We don’t have a timeline for this, but thanks for flagging this as an area of improvement! We might pick this up in an upcoming hackweek soon
Sigh - I spent about 2-3 hours debugging this. This should be much better explained in the Retool docs, and an option to autoconvert to JS numeric types would be much appreciated.
In the meanwhile, here's a workaround, at least for Postgres. In my case the column 'id' was a Postgres bigint. This uses the shorthand cast of id to an integer. Note that id has to come after the *. If the * comes after, it will overwrite the column with the original value (which Retool which interpret as a string).
The workaround above did not work for me... Are there any news about this feature?
Sorting in Retool has proven to be quite a pain for our project because of this issue. We often use the mapped value field to change how our columns look (e.g. 10 products instead of just 10) and uncheck the Sort by mapped value checkbox to sort based on the number. However because of this issue, they are being sorted like [1,10,2,3].
The dev team is looking to add more built-in support for BigInt values in Table instead of having them automatically be converted to strings.
In the meantime though, you might try mapping over your values and manually converting them into numbers with something like this (you'd want to replace yourQuery and quantity with your own values):
Hello @Kabirdas ! Thanks for the reply. I tried this and while it seemed to affected my data (null values were converted to 0), the sorting problem remains
Ahh I see, the dev team is looking to improve some of the sorting options but at the moment setting the column type to HTML will force the values to be sorting as strings. Instead of converting them to numbers you might try padding them with 0's:
That's good to hear! Padding with 0's works for now! This also helped me in the new tables feature, now that "sort by mapped value" checkbox is gone... I hope there are plans to bring it back
Being able to sort my mapped values is part of the team's investigation I'm not sure exactly what it will look like yet but they're definitely considering it!
What column type are you using? And are you currently on cloud or a different version of Retool?
I'm not sure exactly what it will look like yet but they're definitely considering it!