INSERT with placeholders via dbExecute does not work

[stefan-m-lenz]

I try to execute an INSERT statement with placeholders in my Postgres DB. This gives me a syntax error.
This problem occurs also with the example from the DBI package for the DBI::dbExecute function.

The following is the code from the example for DBI::dbExecute, with the first line for the connection replaced with a Postgres data base,

con <- dbConnect(RPostgres::Postgres(),
                 dbname = "test",
                 user = "postgres",
                 password = "password")

dbWriteTable(con, "cars", head(cars, 3))
dbReadTable(con, "cars")   # there are 3 rows
dbExecute(
  con,
  "INSERT INTO cars (speed, dist) VALUES (1, 1), (2, 2), (3, 3)"
)
dbReadTable(con, "cars")   # there are now 6 rows

# Pass values using the param argument:
dbExecute(
  con,
  "INSERT INTO cars (speed, dist) VALUES (?, ?)",
  params = list(4:7, 5:8)
)

dbDisconnect(con)

The first call to dbExecute works, but the second one with the placeholders yields an error:

Error: Failed to prepare query: FEHLER:  Syntax error at »,«
LINE 1: INSERT INTO cars (speed, dist) VALUES (?, ?)

I use PostgreSQL version 14, R version 4.1.2 and a freshly installed DBI and RPostgres package on Windows 10.
Is this a bug or do I miss something?

[krlmlr]

Thanks. Do you need $1 and $2 instead of ? ?

What's the best place to document this?

[stefan-m-lenz]

Thank you very much for the fast response.

Yes, this works:

dbExecute(
  con,
  "INSERT INTO cars (speed, dist) VALUES ($1, $2)",
  params = list(4:7, 5:8)
)

I thought that all SQL dialects use a question mark as placeholder. Well, I was wrong. Thanks for helping me to learn this.

I would suggest to add a simple example for an INSERT or other statement that uses placeholders in the documentation. The README example could be a good place, as using placeholders is a practice that should be encouraged.

Additionally, the postgres-query documentation item ("Execute a SQL statement on a database connection") could be expanded with such a simple INSERT example and a brief explanation could be added there that PostgreSQL uses different placeholders.

[medeirosramos]

if the field is boolean in postgresql, I could pass the parameter TRUE, FALSE or NA. But dbExecute can't handle when the field is NA, an error occurs:

Erro: Failed to fetch row : ERROR:  invalid input syntax for type boolean: "NA"
CONTEXT:  unnamed portal parameter $5 = '...'

[krlmlr]

Thanks, Rodrigo. Can you please open a new issue and include a reproducible example? This is about documenting the placeholders.