5

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?

Curlew
  • 1,022
  • 18
  • 39

1 Answers1

5

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
plannapus
  • 18,529
  • 4
  • 72
  • 94
  • Ahh, a `sapply` was missing. Thanks! – Curlew Nov 27 '14 at 09:43
  • Found out that occasionally I also needed a `dbClearResult` call. So like this: `sapply(queries,function(x) res <- dbSendQuery(con,x);dbClearResult(res)` – Curlew Nov 27 '14 at 11:51
  • 1
    You will need curly braces in that case though `sapply(queries,function(x){res <- dbSendQuery(con,x);dbClearResult(res)})` – plannapus Nov 27 '14 at 11:57
  • 1
    Or better, use `dbGetQuery()` which automatically does the `dbClearResult()` for you – hadley Feb 18 '15 at 03:55
  • Indeed I was assuming that if they were several queries read together from a file they were more likely to be INSERT or UPDATE than SELECT queries, hence the dbSendQuery instead of the dbGetQuery, but you're right dbGetQuery makes in general more sense. – plannapus Feb 18 '15 at 07:48