convert variable to text or string in R-CodePudding

I read data from excel file to connect and use in postgreSQL. I could connect to database and need to query using the variables from excel file. This part is working fine.

I declara a variable from excel file say "school" which I need to use dynamically to query in database. below is my query

sid <- '500'
my_school <- RPostgreSQL::dbGetQuery(conn = con, statement = "SELECT * from school where school_id = sid ")

it works if I use "500" instead of sid but I need to use a dynamic variable.

the error I get is :

Error: Failed to prepare query: ERROR:  column "sid" does not exist
LINE 1: SELECT * from school where school_id = sid 
                                                  ^
HINT:  Perhaps you meant to reference the column "school.db_sid".
>

Could anyone look at this? Thanks.

CodePudding user response：

Use sprintf:

sprintf("SELECT * from school where school_id = %s", sid)
#[1] "SELECT * from school where school_id = 500"

Add quotation marks if appropriate:

sprintf("SELECT * from school where school_id = '%s'", sid)
#[1] "SELECT * from school where school_id = '500'"