RPostgreSQL - Passing Parameter in R to a Query in RPostgreSQL

Question

Question: How do I pass a variable in the RPostgreSQL query?

Example: In the example below I try to pass the date '2018-01-03' to the query

library(RPostgreSQL)

dt <- '2018-01-03'

connect <- dbConnect(PostgreSQL(), 
                 dbname="test",
                 host="localhost",
                 port=5432,
                 user="user", 
                 password="...")
result <- dbGetQuery(connect,
                "SELECT * FROM sales_tbl WHERE date = @{dt}")

Answer 1

You can use paste0 to generate your query and pass it to dbGetQuery:

library(RPostgreSQL)

dt <- '2018-01-03'

connect <- dbConnect(PostgreSQL(), 
  dbname="test",
  host="localhost",
  port=5432,
  user="user", 
  password="...")

query <- paste0("SELECT * FROM sales_tbl WHERE date='", dt, "'")
result <- dbGetQuery(connect, query)