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?