Retain the text values with special character (such as hyphen and space) when updating to PostgreSQL database through R

Question

I want to update a table in PostgreSQL table from a newData dataframe in local through a loop when the id matches in both tables. However, I encountered issues that the text values do not update exactly as our newData to the database. Number is updating correctly but there are 2 issues when updating the text:

1) I have a column for house_nbr and it can be '120-12', but somehow it calculated and updated as '108' which should really be the text '120-12'.

2) I have a column for street_name and it can be 'Main Street', but I received an error that I couldn't resolve. (Error in { : task 1 failed - "Failed to prepare query: ERROR: syntax error at or near "Street")

The database table datatype is in char. It seems something is wrong with special character in the text, such as hyphen and space. Please advise how to retain the character text when updating to a Postgre database. Below is the code I am using. Thanks!

Update <- function(i) {
  con <- dbConnect(RPostgres::Postgres(),
                   user="xxx",
                   password="xxx",
                   host="xxx",
                   dbname="xxx",
                   port=5432)
  text <- paste("UPDATE dbTable SET house_nbr=" ,newData$house_nbr[i], ",street_name=",newData$street_name[i], "where id=",newData$id[i])
  dbExecute(con, text)
  dbDisconnect(con)
}

foreach(i = 1:length(newData$id), .inorder=FALSE,.packages="RPostgreSQL")%dopar%{
  Update(i)
}

Within a string there is nothing special about a dash (-). However if not within single quotes it is not a string but an (arithmetic) expression which would be evaluated then converted to a string for storage. So perhaps you need ""UPDATE dbTable SET house_nbr=" ,'newData$house_nbr[i]...' ( I do not know the language you are using.) But part 2 indicates this is happening code/data frame as the error states "Failed to prepare query,,," Which indicates it never got sent to the database. How are strings represented in you data frame? You may also want to review [ask]. — Belayer, May 20 '20 at 18:03
Both variables are in char in the newData data frame. I was able to get the whole string with your suggestion to put single quotes outside of the variable. "UPDATE dbTable SET house_nbr=' " ,newData$house_nbr[i], " ' ,street_name=...." Work for both part 1 and part 2. Thank you very much! — chichi, May 20 '20 at 21:28

Retain the text values with special character (such as hyphen and space) when updating to PostgreSQL database through R

0 Answers0