Ruby with Postgresql raising NumericValueOutOfRange

Question

I'm trying to do the following query using ruby and pqsql, but when trying a range with more than 1.000 registers I'm receiving an error.

.
.
.
user.left_outer_joins(:coverage_radius).where(id: i).where(
'3959 * acos(
  cos(
    radians(?)
  )
  * cos(
    radians(coverage_radius.latitude)
  )
  * cos(
    radians(?) - radians(coverage_radius.longitude)
  )
  + sin(radians(?))
  * sin(radians(coverage_radius.latitude))
) <= coverage_radius.miles',
job.latitude,
job.longitude,
job.latitude
).count
.
.
.

The error I'm receiving is:

7.0.4/lib/active_record/connection_adapters/postgresql_adapter.rb:768:in `exec_params': PG::NumericValueOutOfRange: ERROR:  input is out of range (ActiveRecord::RangeError)

/var/deploy/proxypics_production/web_head/shared/bundle/ruby/3.2.0/gems/activerecord-7.0.4/lib/active_record/connection_adapters/postgresql_adapter.rb:768:in `exec_params': ERROR:  input is out of range (PG::NumericValueOutOfRange)

I'm dealing with numeric and double numbers, basically doing some maths with latitude/longitude. should I convert everything to double or everything to numeric to solve this?

Answer 1

The error message PG::NumericValueOutOfRange: ERROR: input is out of range (ActiveRecord::RangeError) indicates that you are trying to insert or update a numeric value in a PostgreSQL database that exceeds the allowed range for the column's data type.

We can follow below steps to resolve it:

1. Identify the column causing the issue.
2. Check the column's data type and constraints.
3. Ensure input values are within the allowed range.
4. Alter the data type if necessary via a migration.
5. Add validations to prevent future issues.