PostgreSQL Ruby Heroku Problems

Question

This search condition works fine in MySQL, and SQLite but fails in PostgreSQL:

CONTROLLER:

@inventories = Inventory.search(params[:search], params[:page])

MODEL:

def self.search
paginate    :per_page => 10,
            :page => page, 
            :conditions => ['part_number = ? OR item LIKE ? OR catalognumber LIKE ?', "#{search}", "%#{search}%", "%#{search}%"],
            :order => 'item'
end

IT produces this error in HEROKU's PostgreSQL log:

ActiveRecord::StatementInvalid (PGError: ERROR:  invalid input syntax for integer: ""
2011-06-11T20:25:46+00:00 app[web.1]: : SELECT * FROM "inventories" WHERE (part_number = E'' OR item LIKE E'' OR catalognumber LIKE E'')  ORDER BY item LIMIT 10 OFFSET 0):

How can I fix this?

SOLUTION... This is an issue of using LIKE rather than ILIKE http://www.postgresql.org/docs/8.3/static/functions-matching.html

Tip: use ILIKE instead of LIKE so your searches are not case-sensitive on Heroku. — sscirrus, Commented Jun 11, 2011 at 20:38

Denis de Bernardy · Accepted Answer · 2011-06-11 20:38:40Z

1

Presumably, one of those fields is expecting an integer, and Postgres is a lot less permissive on this front than MySQL. I shouldn't have any quotes. (It should be zero, or no field at all, instead.)

answered Jun 11, 2011 at 20:38

Denis de Bernardy

79k14 gold badges138 silver badges158 bronze badges

Part Number is an integer. The rest are strings
– JZ.
Commented Jun 11, 2011 at 20:41
Cast it: part_number = cast(? as int)
– Denis de Bernardy
Commented Jun 11, 2011 at 20:42
adding search.to_i, fixes the error. But I have no capacity to search for strings. For instance say i search "Item"... search isn't working.
– JZ.
Commented Jun 11, 2011 at 20:43
I was actually meaning in your sql statement, just as above.
– Denis de Bernardy
Commented Jun 11, 2011 at 20:46
That said, I don't know much about ruby (yet: learning...), so I might be getting the RoR/Active Record thing all wrong, i.e. take it with a pinch of salt. :-)
– Denis de Bernardy
Commented Jun 11, 2011 at 20:48

| Show 1 more comment

Collectives™ on Stack Overflow

PostgreSQL Ruby Heroku Problems

1 Answer 1

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related