Rails 7.2 PG::InvalidTextRepresentation with integer column & binding

Question

We are planning the migration to Rails 7.2.2.1 (we currently run on 7.1.5.1).

We have noticed some errors during the CI run while querying with binding variable on integer column.

For example :

# Loading development environment (Rails 7.2.2.1)
FulfillmentZone.where("fulfillment_zones.maximum_zone_quantity >= :quantity", quantity: 15.0).to_a
  FulfillmentZone Load (0.9ms)  SELECT "fulfillment_zones".* FROM "fulfillment_zones" WHERE (fulfillment_zones.maximum_zone_quantity >= $1)  [[nil, 15.0]]
ActiveRecord::StatementInvalid: PG::InvalidTextRepresentation: ERROR:  invalid input syntax for type integer: "15.0" (ActiveRecord::StatementInvalid)
CONTEXT:  unnamed portal parameter $1 = '...'

from /Users/jlamarque/.rbenv/versions/3.3.7/lib/ruby/gems/3.3.0/gems/activerecord-7.2.2.1/lib/active_record/connection_adapters/postgresql_adapter.rb:912:in `exec_prepared'
Caused by PG::InvalidTextRepresentation: ERROR:  invalid input syntax for type integer: "15.0"
CONTEXT:  unnamed portal parameter $1 = '...'

from /Users/jlamarque/.rbenv/versions/3.3.7/lib/ruby/gems/3.3.0/gems/activerecord-7.2.2.1/lib/active_record/connection_adapters/postgresql_adapter.rb:912:in `exec_prepared'

The same query does not raise in 7.1.5.1 :

# Loading development environment (Rails 7.1.5.1)
FulfillmentZone.where("fulfillment_zones.maximum_zone_quantity >= :quantity", quantity: 15.0).to_a
  FulfillmentZone Load (1.4ms)  SELECT "fulfillment_zones".* FROM "fulfillment_zones" WHERE (fulfillment_zones.maximum_zone_quantity >= 15.0)
#=> [#<FulfillmentZone:0x00000001293d0ea8 id: 1, fulfillment_service_id: 1, name: "France", deleted_at: nil, created_at: Wed, 18 Sep 2024 12:06:57.819485000 CEST +02:00, updated_at: Wed, 18 Sep 2024 12:06:57.819485000 CEST +02:00, maximum_zone_quantity: 100>,
 #<FulfillmentZone:0x0000000129218c00 id: 3, fulfillment_service_id: 3, name: "FRANCE", deleted_at: nil, created_at: Tue, 31 Dec 2024 09:32:23.843419000 CET +01:00, updated_at: Tue, 31 Dec 2024 09:32:23.843419000 CET +01:00, maximum_zone_quantity: 100>]

Any ideas ?

I investigated inside postgresql adaptor libs of activerecord (ActiveRecord::ConnectionAdapters::PostgreSQL::Quoting) and noticed that the result of casting was returned by abstract quoting module (method type_cast). In the case of a numeric value, there is no casting, the value is returned unchanged, raising an invalid statement after.

Please provide enough code so others can better understand or reproduce the problem. — Community
– Community Bot, Commented Jun 4 at 13:00

engineersmnky · Accepted Answer · 2025-06-04 15:01:11Z

1

Type Cast in your code

If you have control over the 15.0 and know that it should be an Integer then you could just convert it to an Integer via to_i

Change the way you are querying

Any particular reason you are using the String version of where? Seems like:

FulfillmentZone.where(maximum_zone_quantity: 15.0..)

would work just fine and would go through type casting.

Another alternative would be to use Arel:

FulfillmentZone.where(FulfillmentZone.arel_table[:maximum_zone_quantity].gteq(15.0))

I think your primary issue is that using the String version rails does not introspect your string to determine that fulfillment_zones.maximum_zone_quantity is an Integer column and there for it just quotes it and let's the database try and figure it out.

Behavioral Change between versions

I think I found where this behavior changed:

Rails 7.2 removed ActiveRecord::ConnectionAdapters::Abstract#quote_bound_value, the previous behavior was to take a bound value and run it through the #quote method which treats different inputs differently from a SQL quoting perspective.

In 7.1.5 you should have received a deprecation warning (Source) and as you can see in the source code it uses quote(cast_value(value)).

In 7.2.2 the deprecation was removed and the implementation is now simply the return from cast_value which states in the comments:

Cast a value to be used as a bound parameter of unknown type. For example, MySQL might perform dangerous castings when comparing a string to a number, so this method will cast numbers to string.

edited Jun 4 at 15:01

answered Jun 4 at 13:05

engineersmnky

30.5k2 gold badges42 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user30719238 Jun 4 at 13:31

Thanks for your answer. Yes sure, I could apply to_i on my variable. But on the whole scope of the application, it is quite complex to identify all similar cases. We do not have enough coverage to raise each of them. The example I gave in console is simple, the queries we use are more complex, and rewriting them all with Arel would need a big amount of time...

engineersmnky Jun 4 at 14:56

@user30719238 Unfortunately based on your implementation and the deprecation followed by removal of a mechanism you are relying on you are kind of stuck here. Changes are going to need to be made to your code base to remain functional post upgrade. I have never been a big supporter of using Strings in Rails queries for this specific reason (among others).

Collectives™ on Stack Overflow

Rails 7.2 PG::InvalidTextRepresentation with integer column & binding

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related