RangeError - integer 364834158948831939 too big to convert to `int'

[John Smart]

I'm having a hard time creating queries that work with 64-bit integers, Int8, bigint, (pick your terminology).

$pg.exec_params(<<-SQL, fig_file.id, fig_file.checkpoint_id)
  BEGIN;
  UPDATE checkpoints
  SET client_meta = sub.client_meta
  FROM (
    SELECT checkpoint->'client_meta' AS client_meta FROM fig_files WHERE id = ?
  ) AS sub
  WHERE checkpoints.id = ?;
  COMMIT;
SQL

With this query, I would figure the PG gem would be able to marshal a 64-bit integer correctly to PG.  Is there a better way to accomplish this?

[John Smart]

I forgot to mention that the above results in an Error:
"RangeError - integer 364834158948831939 too big to convert to `int'"

[John Smart]

Does anyone know how to convert to bigint values in pg?

[Lars Kanis]

You must use "$1" as the placeholder for bind parameters (not "?"). It should work like this:

c=PGconn.new

c.exec("CREATE TEMP TABLE bigint_test (big bigint)")

c.exec_params("INSERT INTO bigint_test (big) VALUES ($1) RETURNING big", [364834158948831939]).to_a # => [{"big"=>"364834158948831939"}] 

c.exec_params("SELECT * FROM bigint_test WHERE big=$1", [364834158948831939]).to_a # => [{"big"=>"364834158948831939"}] 

c.exec_params("SELECT $1::bigint", [364834158948831939]).to_a
# => [{"int8"=>"364834158948831939"}]

If the PostgreSQL server can not find the parameter type from the column type, you need to cast the value as "$1::bigint", as seen in the last line above.

Hope this helps.