postgres - operator does not exist: double precision ~~ unknown

Question

ActionView::Template::Error (PG::UndefinedFunction: ERROR: operator does not exist: double precision ~~ unknown 2016-04-10T23:45:59.506005+00:00 app[web.1]: LINE 1: ... = "trackers"."category_id" WHERE (categories.tag LIKE '1.%'...

this is the error i get when i try to run this line of code here

Tracker.group(:category_id).joins(:category).where('categories.tag LIKE ? AND user_id = ?', "#{tag.to_i}.%", current_user.id)

tag is of type float, and i typecast it to an integer in order to check for tags 1.1, 1.2, 1.3 etc

so in the example above I type cast tag with value 1.0 to be 1, so i can search for tags that are like 1.1, 1.2 etc

I am using postgres on heroku that gives this error. locally i use sqlite3 and it works just fine.

how can i overcome this?

You're using a string function (LIKE) on a numeric type (float), postgres being a grown up DB (compared to SQLL) will negate such searches. If you're searching for all 1.0 to 1.9 why don't you execute where tag >= 1.0 and tag <= 1.9 — Ed Baker
– Ed Baker, Commented Apr 11, 2016 at 0:15
yeah i now get that, but i need it to work for many values not only between 1.0, 1.9. it could be between 2.0 and 2.9 or whatever, thats why i need it to be dynamic. is there a way to do it or? — Petros Kyriakou
– Petros Kyriakou, Commented Apr 11, 2016 at 0:16
Since you're in rails, why don't you sort out the dynamic-ness in rails first then send that to the ORM. The syntax you provided already accepts any parameters (eg: WHERE tag between ? and ?), so before you request the data from the ORM, sort out in rails the high and lows. The query is already setup for something to be dynamic. — Ed Baker
– Ed Baker, Commented Apr 11, 2016 at 0:24
@EdBaker thank you this helped me fix the problem. if you want to make an answer out of it, please do, that way i will help you too. cheers — Petros Kyriakou
– Petros Kyriakou, Commented Apr 11, 2016 at 10:18

Ed Baker · Accepted Answer · 2016-04-12 00:41:03Z

1

Since you're in rails, sort out the dynamic-ness in rails first then send that to the ORM. The syntax you provided already accepts any parameters (eg: WHERE tag between ? and ?), so before you request the data from the ORM, sort out in rails the high and lows. The query is already setup for something to be dynamic.

answered Apr 12, 2016 at 0:41

Ed Baker

6622 gold badges6 silver badges20 bronze badges

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

Collectives™ on Stack Overflow

postgres - operator does not exist: double precision ~~ unknown

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related