Postgrex rejects decimal values for comparison with integer columns?

mmmries

I work on a system where customers are allowed to dynamically query their own data and we use Ecto to build these dynamic queries. One thing that I noticed recently is if I have table with an integer columns, and the customer tries to filter it with something like my_integer > 14.5 I get an ecto error:

** (DBConnection.EncodeError) Postgrex expected an integer in -9223372036854775808..9223372036854775807, got #Decimal<10.0>. Please make sure the value you are passing matches the definition in your table or in your query or convert the value accordingly.

This error makes sense to me in general, but Postgresql does allow these kinds of filters. For example, I made a test table with some rows that contain 1or 2 in the integer column, and I can query it on psql like this:

postgres@[local]:5432/intdec# SELECT * FROM test_table WHERE integer_col <= 1.85;
┌─────────────┬─────────────┐
│ decimal_col │ integer_col │
├─────────────┼─────────────┤
│        1.00 │           1 │
│        1.10 │           1 │
│        1.50 │           1 │
└─────────────┴─────────────┘
(3 rows)

Postgres itself is not just casting to an integer, it’s actually implementing some intuitive behavior where it handles > and < differently. So I’m wondering why Postgrex does not allow this kind of query to be executed?

5 comments

#ecto #decimal

0 1242 5

2023-05-08 20:14:31 UTC

Showing Posts 1 to 5

Show Best Posts
Show All (oldest first)
Show All (newest first)

mmmries

Looking into this a bit further, it looks like this behavior isn’t the same for prepared statements. The prepared statements seem to do a simple “round” to an integer value and then filter using that integer:

postgres@[local]:5432/intdec# prepare foo as SELECT * FROM test_table WHERE integer_col <= $1;
PREPARE
Time: 0.654 ms
postgres@[local]:5432/intdec# EXECUTE foo(1.45);
┌─────────────┬─────────────┐
│ decimal_col │ integer_col │
├─────────────┼─────────────┤
│        1.00 │           1 │
│        1.10 │           1 │
│        1.50 │           1 │
└─────────────┴─────────────┘
(3 rows)

Time: 0.688 ms
postgres@[local]:5432/intdec# EXECUTE foo(1.9);
┌─────────────┬─────────────┐
│ decimal_col │ integer_col │
├─────────────┼─────────────┤
│        1.00 │           1 │
│        1.10 │           1 │
│        1.50 │           1 │
│        1.90 │           2 │
│        2.10 │           2 │
└─────────────┴─────────────┘
(5 rows)

Time: 0.466 ms

That seems to match what I read on this issue suggesting a better error message Feature Request: More descriptive error message when a value cannot be encoded into the database · Issue #562 · elixir-ecto/postgrex · GitHub. Prepared statements just define a type for each argument, and then the client must send that type for that argument.

Post #1

LostKobrakai

You can work around that by typecasting the parameter. Instead of passing in just the value the user provided you could pass in dynamic(type(^value, :integer)) vs. dynamic(type(^value, :float)) depending on what you got. It means you need to know up front what values you accept, but you at least don’t need to depend on the db guessing.

Post #2

mmmries

Thanks @LostKobrakai for the suggestion. Typecasting the decimal into an integer would work, but I think it can create some surprising result for instance my_integer < type(1.4, :integer) would not match rows with my_integer = 1 because it typecasts the decimal to 1 and then it ends up checking 1 < 1 and rejecting the row.

But I think I can do type(my_integer, :decimal) < Decimal.new("1.4") and that I think that will work intuitively for equals, less than and greater than, etc comparisons.

Post #3

benwilson512

Author of Craft GraphQL APIs in Elixir with Absinthe

I wonder if it’s because Postgrex is using the postgres binary protocol for communication, and so it’s going to need to be strict about how to encode types?

Post #4

josevalim

Creator of Elixir

I wonder if it’s because Postgrex is using the postgres binary protocol for communication, and so it’s going to need to be strict about how to encode types?

That’s exactly it. EXECUTE (...) is automatically casting it. Postgrex tends to be more conservative.

Post #5

— All posts loaded —