Search code examples
rubypg

ruby pg can't determine data type for parameters

I have simple ruby script.

@connect = PGconn.connect(@host, 5432, '', '', @db, @dbuser, @password)
sql = "SELECT count(brand) as count FROM my_tbl WHERE time >= NOW() - 
INTERVAL '$1' HOUR GROUP BY my_tbl.brand HAVING count(brand) > $2 ORDER BY count DESC"
res = @connect.exec_params(sql,[1,2])

Whenever I run this part of code I'm having error 

could not determine data type of parameter $1

What am I missing?

I tried to put strings instead of [1,2] it still didn't work. $1 has apostrophe but that SHOULD NOT be a problem. If it is (I can't remove apostrophe) then why?

P.S. The code is based by PG docs

Solution

  • Try this 

    @connect = PGconn.connect(@host, 5432, '', '', @db, @dbuser, @password)
sql = "
  SELECT count(brand) as count 
  FROM my_tbl 
  WHERE time >= NOW() - $1::INTERVAL
  GROUP BY my_tbl.brand 
  HAVING count(brand) > $2::INTEGER 
  ORDER BY count DESC
"
result = @connect.exec_params(sql, ['1 HOUR', 2])
    • specify the data type(s) with the parameter token
    • pass the 'HOUR' portion of the interval as the parameter value

    Update

    P.S. in PG you must quote number of hours. So it will be '1' HOUR as first parameter.

    I am using postgres 9.2.4 and that is not the case, i.e. select '1 HOUR'::INTERVAL; is valid in psql, note the quote is around both the integer value and the interval type

    The thing is that I'm new to ruby (not programming) and i'm trying to understand philosophy of it.

    I don't recommend learning philosophy of ruby starting with database interaction

    If you do want to get started with ruby and postgres (without using rails) I still recommend using ActiveRecord, but use the raw connection object