query syntax "insert into" with R

Question

I want to add real value calculated earlier on a postgresql database

Imp1=5/4
req=paste("INSERT INTO important (num_cluster,type,indicateur_imp) VALUES (1,'temperature',Imp1)")
resultat=dbGetQuery(con,req)

error

Error in postgresqlExecStatement(conn, statement, ...) : 
  RS-DBI driver: (could not Retrieve the result : ERREUR:  la colonne Â« imp1 Â» n'existe pas
LINE 1: ...m_cluster,type,indicateur_imp) VALUES (1,'temperature',Imp1)
                                                                  ^
)
Warning message:
In postgresqlQuickSQL(conn, statement, ...) :
  Could not create executeINSERT INTO important (num_cluster,type,indicateur_imp) VALUES (1,'temperature',Imp1)

Typically, people will build queries in R using either paste or sprintf. — joran
– joran, Commented Jan 29, 2015 at 17:33
yes I used "paste" the error is on the value of "Imp1" how to add a real number on queries? — user26480
– user26480, Commented Jan 29, 2015 at 17:36
You "used" paste, but you didn't use paste. Compare paste("some text Imp") and paste("some text",Imp). — joran
– joran, Commented Jan 29, 2015 at 17:38
req=paste("INSERT INTO important (num_cluster,type,indicateur_imp) VALUES (1,'temperature' ))",Imp1,sep="") resultat=dbGetQuery(con,req) — user26480
– user26480, Commented Jan 29, 2015 at 17:56

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

I think should give you what you're looking for:

Imp1 <- 32.1
req  <- paste("INSERT INTO important (num_cluster,type,indicateur_imp) 
VALUES (1,'temperature',",Imp1, ")",sep="")

That will output:

[1] "INSERT INTO important (num_cluster,type,indicateur_imp) VALUES (1,'temperature',32.1)"

paste() will take any variable and return it's value as a string, and you chain items together with commas. You can end the statement with a sep= parameter. To be the most literal (which I like doing for queries) I just use sep = "" and type the whole thing literally. So If I had multiple values I wanted to insert I would do something like this:

var1 <- round(rnorm(5))
var2 <- c("'temp'","'pressure'","'precip'","'volume'","'mass'")
for(i in 1:length(var1)){
req <- paste("INSERT INTO important (num_cluster,type,indicateur_imp) VALUES   
(1,",var2[i],",",var1[i],")",sep="")
# not run, but do to send your query 
# dbSendQuery(conn,req)
print(req)
}

That will send the following queries:

[1] "INSERT INTO important (num_cluster,type,indicateur_imp) VALUES (1,'temp',0.09)"

[1] "INSERT INTO important (num_cluster,type,indicateur_imp) VALUES (1,'pressure',0.04)"

[1] "INSERT INTO important (num_cluster,type,indicateur_imp) VALUES (1,'precip',-1.2)"

[1] "INSERT INTO important (num_cluster,type,indicateur_imp) VALUES (1,'volume',0.78)"

[1] "INSERT INTO important (num_cluster,type,indicateur_imp) VALUES (1,'mass',1.15)"

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jan 29, 2015 at 18:20

emhart

8045 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

emhart Over a year ago

Note that when you send strings to a query, they are stored in the vector with double and single quotes e.g. "'temp'" not "temp" (assuming you want to send a string.

Collectives™ on Stack Overflow

query syntax "insert into" with R

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related