0

I have the following dataframe:

library(rpostgis)
library(RPostgreSQL)
library(glue)
df<-data.frame(elevation=c(450,900),
               id=c(1,2))

Now I try to upload this to a table in my PostgreSQL/Postgis database. My connection (dbConnect) is working for "SELECT"-Statements properly. However, I tried two ways of updating a database table with this dataframe and both failed.

First:

 pgInsert(postgis,name="fields",data.obj=df,overwrite = FALSE, partial.match = TRUE,
         row.names = FALSE,upsert.using = TRUE,df.geom=NULL)

2 out of 2 columns of the data frame match database table columns and will be formatted for database insert.
Error: x must be character or SQL

I do not know what the error is trying to tell me as both the values in the dataframe and table are set to integer.

Second:

sql<-glue_sql("UPDATE fields SET elevation ={df$elevation} WHERE 
+               id = {df$id};", .con = postgis)
> sql
<SQL> UPDATE fields SET elevation =450 WHERE 
id = 1;
<SQL> UPDATE fields SET elevation =900 WHERE 
id = 2;
dbSendStatement(postgis,sql)
<PostgreSQLResult>

In both cases no data is transferred to the database and I do not see any Error logs within the database.

Any hint on how to solve this problem?

Bertil Baron
  • 4,923
  • 1
  • 15
  • 24
Johannes42
  • 29
  • 4

1 Answers1

0

It is a mistake from my site, I got glue_sql wrong. To correctly update the database with every query created by glue_sql you have to loop through the created object like the following example:

for(i in 1:max(NROW(sql))){
   dbSendStatement(postgis,sql[i])
}
Johannes42
  • 29
  • 4