In R, how to pass PostgreSQL column contents to dbGetQuery using param

Question

I am trying to query my PostgreSQL DB in R, using dbGetQuery(). Below are what I have done so far:

This command ran through without issue I have it here just to show how the database was queried

test_db = dbPool(drv = dbDriver("PostgreSQL", max.con = 100),
           dbname = "TEST",
           host = "localhost",
           user = "postgres",
           password = "password",
           idleTimeout = 3600000
        )    

This command ran with error

dbGetQuery(test_db, 
       "select * from public.table1 where tag = ?", 
       params = 'tag_col_content1') 

Error in postgresqlExecStatement(conn, statement, ...) : 
RS-DBI driver: (could not Retrieve the result : ERROR:  syntax error at end of input
LINE 1: ...lect * from public.table1 where tag = ?
                                                  ^
)
NULL
Warning message:
In postgresqlQuickSQL(conn, statement, ...) :
  Could not create execute: select * from public.table1 where tag = ?

I have to suspect it has something to do with the double/single quotes, but could not figure out what exactly. I also tried the following which works:

dbGetQuery(test_db, 
       "select * from public.table1 where tag = 'tag_col_content1'") 

Please note that it is important for me to pass value to the SQL statement because eventually this line will be used interactively in Rshiny. Thank you very much

Answer 1

In PostgreSQL, the official way to spell place holders is $1, $2, ... $9,... not ?. Some drivers will automatically parse and transform queries for you, but evidently not this one.

When I used $1 in place of ?, it works.