Query with text being cast as decimal

Queries and Resources
1

I'm using AirTable and SyncInc. to get my data into Retool. To quote the Sync documentation: " Airtable Formulas and Lookup Values can be numbers, strings, dates, or timestamps. So these fields are stored in your Postgres database as text and text[] columns, respectively."

So for the sake of simplicity I have 'Owners' and I have the 'Acreage Owned'. Acreage Owned is a formula field in AirTable, so instead of a number like 123.45, it's stored as a text array and shows up as "123.45".

I'm trying to run a basic query where you input a minimum and maximum acreage and it returns all the owners in that range. Here is my code:

select owners.owner_name, owners.acreage_owned[1]::DECIMAL
from owners
where owners.acreage_owned[1] > {{textinput8.value}} and owners.acreage_owned[1] < 
{{textinput9.value}};

I casted acreage_owned as a decimal and the query runs and returns the correct data...mostly.

If I have a minimum value of 250 and a maximum value of 350, the query will also return values where the first 3 digits fall into that range. For example, it will also include acreage_owned values like 2,500 or 34,260.

Can someone help me understand why it's only reading the first x number of digits (based on my inputs) and not treating the entire thing like a number?

Sorry if this is unclear. Happy to try and clarify further. Thanks.

2

Textinput components will usually have their values stored as strings, which might be causing some odd behavior here.

What happens with a query like this?

select owners.owner_name, owners.acreage_owned[1]::DECIMAL
from owners
where owners.acreage_owned[1] > {{parseFloat(textinput8.value)}} and owners.acreage_owned[1] < 
{{parseFloat(textinput9.value)}};
3

Adding the parseFloat did not seem to change the results. It’s still returning anything where the first x number of digits fall into the range I’ve specified. Appreciate the help.

4