1

Question: How can RPostgreSQL be used to query a database table based on a variable containing a vector of primary keys?

Example: In this I have the following code which works for one instance, but does not work when I try to vectorize it.

library(RPostgreSQL)

Pkey <- 100231

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

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

Example Vectorization Pkey (Does not work):

library(RPostgreSQL)

Pkey <- list$Pkey # This is the change.

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

query <- paste0("SELECT * FROM sales_tbl WHERE id='", Pkey, "'")
result <- dbGetQuery(connect, query)
Daniel
  • 83
  • 1
  • 1
  • 10
  • if `Pkey` is a vector then your `query` object will also be a vector, which will not work with `dbGetQuery` (the `statement` argument needs to be a single string), so you need to paste your `query` string together in such a way that it makes a single string with a valid SQL command. – Chris Holbrook Aug 03 '18 at 14:37

1 Answers1

0

With the glue package:

query <- glue::glue_sql("SELECT * FROM sales_tbl WHERE id IN ({Pkey*})",
                        .con = connect)

Source and recommended read: http://db.rstudio.com/best-practices/run-queries-safely/

Aurèle
  • 12,545
  • 1
  • 31
  • 49