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