14

Did some googling for about half a day and I can't find any sample of a prepared INSERT statement using the pg gem (postgresql ruby gem).

I tried this (after looking at the gem docs):

def test2
    conn = PG.connect( dbname: 'db1' )
    conn.prepare("statement1", 'INSERT INTO table1 (id, name, profile) VALUES (?, ?, ?)')
end

But I get the following error:

pgtest.rb:19:in `prepare': ERROR:  syntax error at or near "," (PG::Error)
LINE 1: INSERT INTO table1 (id, name, profile) VALUES (?, ?, ?)
                                                        ^
from pgtest.rb:19:in `test2'
from pgtest.rb:25:in `<main>'
iphone007
  • 496
  • 1
  • 5
  • 15
  • Can you post the whole file please? That might help, since its a syntax error — it may have started farther back – Jwosty May 31 '12 at 21:20
  • @Jwosty: The `ERROR: ... (PG::Error)` indicates that error is coming from `pg` rather than Ruby. – mu is too short May 31 '12 at 21:30
  • Oh, that makes sense... It looked like it was a syntax mistake on the OP's part. I didn't realize that the gem was throwing the error (I've never used this one before); thanks! – Jwosty Jun 01 '12 at 01:05

1 Answers1

34

The pg gem wants you to use numbered placeholders ($1, $2, ...) rather than positional placeholders (?):

conn = PG.connect(:dbname => 'db1')
conn.prepare('statement1', 'insert into table1 (id, name, profile) values ($1, $2, $3)')
conn.exec_prepared('statement1', [ 11, 'J.R. "Bob" Dobbs', 'Too much is always better than not enough.' ])

The fine manual has this to say:

- (PGresult) prepare(stmt_name, sql[, param_types ])
[...]
PostgreSQL bind parameters are represented as $1, $1, $2, etc., inside the SQL query.

And again for exec_prepared:

PostgreSQL bind parameters are represented as $1, $1, $2, etc., inside the SQL query. The 0th element of the params array is bound to $1, the 1st element is bound to $2, etc.

mu is too short
  • 426,620
  • 70
  • 833
  • 800
  • 1
    Thanks! That did the trick! Doh, I don't know how I missed that! – iphone007 Jun 01 '12 at 00:21
  • When is it worth it to prepare the statement prior to executing it? – Martin Velez Jul 28 '12 at 11:01
  • 1
    @Martin: If you want to execute the same statement several times with different values. Some DB interfaces require explicit prepared statement use to use placeholders at all but the `pg` gem lets you use placeholders with [`exec`](http://rubydoc.info/gems/pg/PG/Connection#exec-instance_method). – mu is too short Jul 28 '12 at 19:05
  • @mu Thanks! I ran a small experiment: I ran a program 3 times with `exec`; about 1600 `exec` calls. I converted the `exec` calls to `prepare`, and `exec_prepared`, and reran program a 3 times. On average, the version with `prepare` calls was a bit slower, 1%. I remain stumped. – Martin Velez Aug 03 '12 at 23:08
  • @Martin were you using different parameter values for all of the 1,600 exec calls? – David Aldridge Oct 31 '12 at 19:57
  • 2
    @DavidAldridge Yes, I was using different values. However, I realize now that the latency was because they were writes. It masked the latency of preparing. When I do it with reads, I get a noticeable improvement. – Martin Velez Nov 27 '12 at 18:05
  • Nice answer! Want to add that prepared statements live only during and within SESSION. So you can not create permanent prepared statement. I think this is a bit related to question so decided to mention for those who are not aware of this, like me 5 minutes ago. – Yuriy Vasylenko Oct 02 '15 at 14:51