r-dbi / r-dbi/RPostgres

INSERT with placeholders via dbExecute does not work

Open
#391 4 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

docs help wanted
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

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.