时间戳

首页 > 解决方案 > unable to update PostgreSQL table value with R's sys.time as a character using dbconnect

r - unable to update PostgreSQL table value with R's sys.time as a character using dbconnect

问题描述

i am using the below code for connecting to PostgreSQL from R.

library(DBI)
con <- dbConnect(RPostgres::Postgres(),dbname = 'postgres', 
         host = host_name, 
         port = 5432, 
         user = user_name,
         password = password)

by using the above connection i tried to update the PostgreSQL table with sys.time() in R as character.

For that i am sending the below query to database.

dbSendQuery(con,paste0("update job_status set start_dttm=",as.character(sys.time()) ," where job_name='job';"))

but it throws the following error for me

Failed to prepare query: ERROR:  syntax error at or near "04"
LINE 1: update job_status set start_dttm = 2019-03-04 04:50:12 where...

Can anyone help me to tackle this issue.

Thanks in advance.

标签： rpostgresqlsql-update

解决方案

Valid SQL requires quotes around the date:

update job_status set start_dttm='2019-03-04 04:50:12';

Therefore, you would need

paste0("update job_status set start_dttm='", as.character(sys.time(), "';")

The above query is safe from SQL injection since sys.time() should be safe. However, in general (and especially when arguments are derived from user input), it is best to use parametrized SQL to avoid SQL injection.

推荐阅读