heroku - postgres - operator does not exist

Question

I decided to give heroku a try (to save time) but I discovered they use PostgreSQL which I know little about. The geocoder gem works great with MySQL but PostgreSQL is throwing an error.

I think this error is saying lat is not defined?

ActiveRecord::StatementInvalid (PGError: ERROR:  operator does not exist: numeric - character varying
LINE 1: ...58.755864232 * 2 * ASIN(SQRT(POWER(SIN((37.358151 - lat) * P...

Full log

2011-06-07T09:18:52-07:00 heroku[nginx]: GET /api/v1/locations.json?point=37.358151%2C%20-121.628265%20&radius=24.374397 HTTP/1.1 | 66.192.149.18 | 963 | http | 500
2011-06-07T16:19:12+00:00 app[web.1]: 
2011-06-07T16:19:12+00:00 app[web.1]: 
2011-06-07T16:19:12+00:00 app[web.1]: Started GET "/api/v1/locations.json?point=37.358151%2C%20-121.628265%20&radius=24.374397" for 66.192.149.18 at Tue Jun 07 09:19:12 -0700 2011
2011-06-07T16:19:12+00:00 app[web.1]: 
2011-06-07T16:19:12+00:00 app[web.1]: ActiveRecord::StatementInvalid (PGError: ERROR:  operator does not exist: numeric - character varying
2011-06-07T16:19:12+00:00 app[web.1]: LINE 1: ...58.755864232 * 2 * ASIN(SQRT(POWER(SIN((37.358151 - lat) * P...
2011-06-07T16:19:12+00:00 app[web.1]:                                                              ^
2011-06-07T16:19:12+00:00 app[web.1]: HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
2011-06-07T16:19:12+00:00 app[web.1]: : SELECT  *, 3958.755864232 * 2 * ASIN(SQRT(POWER(SIN((37.358151 - lat) * PI() / 180 / 2), 2) + COS(37.358151 * PI() / 180) * COS(lat * PI() / 180) * POWER(SIN((-121.628265 - lng) * PI() / 180 / 2), 2) )) AS distance, CAST(DEGREES(ATAN2( RADIANS(lng - -121.628265), RADIANS(lat - 37.358151))) + 360 AS decimal) % 360 AS bearing FROM "locations" WHERE (lat BETWEEN 37.0053760059938 AND 37.7109259940062 AND lng BETWEEN -122.072086383576 AND -121.184443616424) GROUP BY locations.id,locations.name,locations.bounding_box,locations.created_at,locations.updated_at,locations.street,locations.postal,locations.city,locations.state,locations.country,locations.description,locations.lat,locations.lng,locations.phone,locations.has_lights,locations.is_free,locations.is_outdoors,locations.are_pads_required,locations.has_concrete,locations.has_wood,locations.cd_page_id HAVING 3958.755864232 * 2 * ASIN(SQRT(POWER(SIN((37.358151 - lat) * PI() / 180 / 2), 2) + COS(37.358151 * PI() / 180) * COS(lat * PI() / 180) * POWER(SIN((-121.6
2011-06-07T16:19:12+00:00 app[web.1]: 28265 - lng) * PI() / 180 / 2), 2) )) <= 24.374397 ORDER BY 3958.755864232 * 2 * ASIN(SQRT(POWER(SIN((37.358151 - lat) * PI() / 180 / 2), 2) + COS(37.358151 * PI() / 180) * COS(lat * PI() / 180) * POWER(SIN((-121.628265 - lng) * PI() / 180 / 2), 2) )) ASC LIMIT 100):

Denis de Bernardy · Accepted Answer · 2011-06-09 14:30:46Z

1

You need to cast it:

37.358151 - lat::numeric

answered Jun 9, 2011 at 14:30

Denis de Bernardy

79.2k14 gold badges138 silver badges158 bronze badges

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

Comments

LikeMaBell · Accepted Answer · 2012-01-12 16:24:28Z

1

I had a similar error. It was caused by my specifying foreign id's in my migrations as strings instead of integers.

To fix it, I changed:

create_table :these_objects do |t|
  t.string:user_id
end

to

create_table :these_objects do |t|
  t.integer :user_id
end

answered Jan 12, 2012 at 16:24

LikeMaBell

1,6092 gold badges14 silver badges24 bronze badges

2 Comments

LikeMaBell Over a year ago

SQLite never complained about it in development, but Postgres did.

mu is too short Over a year ago

SQLite's type system is pretty loose, PostgreSQL's is not. The solution is to (as you say) use proper types everywhere and ideally, develop on top of the same database that you're deploying on.

betoharres · Accepted Answer · 2013-12-20 00:07:16Z

0

I had the same problem. You probably have your latitude and longitude field as string, so you need to check if you lat and long fields are floats at your migration file.

create_table :place do |t|
  t.text :name
  t.float :latitude
  t.float :longitude
  t.timestamps
end

answered Dec 20, 2013 at 0:07

betoharres

1,8432 gold badges22 silver badges27 bronze badges

Collectives™ on Stack Overflow

heroku - postgres - operator does not exist

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related