Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to execute sql query files via RPostgreSQL

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?

like image 219
Curlew Avatar asked Nov 26 '14 23:11

Curlew


1 Answers

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
like image 90
plannapus Avatar answered Oct 13 '22 00:10

plannapus