I am accessing my PostgreSQL database (9.3) via R using the RPostgreSQL package.
I have a few very long and big sql queries (several MB big. generated from raster2pgsql).
How can I send / execute sql query files as statement within R?
The normal way
\i query.sql
does not seem to work via dbSendQuery.
I tried to read in the whole sql file as character vector via readLines, however this also fails, because dbSendQuery only supports a single command apparently?
dbSendQuery or dbGetQuery is just for the "SQL" part, not the psql commands such as \i.
In your case the simplest is indeed to use readLines but then wrap dbGetQuery in a sapply call.
con <- dbConnect(...) #Fill this as usual
queries <- readLines("query.sql")
sapply(queries, function(x) dbGetQuery(con,x))
dbDisconnect(con)
Since I use this very often, I have a shortcut for this in my .Rprofile file:
dbGetQueries<-function(con,queries)sapply(queries,function(x)dbGetQuery(con,x))
Of course, you can also go the system way:
system("psql -U username -d database -h 127.0.0.1 -p 5432 -f query.sql") #Remember to use your actual username, database, host and port
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With