INSERT with placeholders via dbExecute does not work
Nobody has claimed this yet.
- Dominant language
- R
- Stars
- 343
- Forks
- 82
- Avg merge
- 11h 26m
- Merged PRs (30d)
- 9
Description
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?
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start by reproducing the reported dbExecute call with the DBI example and PostgreSQL connection described in the issue. Inspect how RPostgres handles parameterized INSERT statements and compare that behavior with DBI expectations. Done means the placeholder case is either supported correctly or its documented limitation is made clear, with regression coverage if the behavior changes.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- postgresql, r
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 42/100