How to execute sql query files via RPostgreSQL

Question

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?

plannapus · Accepted Answer · 2015-02-18 07:51:27Z

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

edited Feb 18, 2015 at 7:51

answered Nov 27, 2014 at 8:02

plannapus

18.8k4 gold badges76 silver badges96 bronze badges

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

5 Comments

Curlew Over a year ago

Ahh, a sapply was missing. Thanks!

Curlew Over a year ago

Found out that occasionally I also needed a dbClearResult call. So like this: sapply(queries,function(x) res <- dbSendQuery(con,x);dbClearResult(res)

plannapus Over a year ago

You will need curly braces in that case though sapply(queries,function(x){res <- dbSendQuery(con,x);dbClearResult(res)})

hadley Over a year ago

Or better, use dbGetQuery() which automatically does the dbClearResult() for you

plannapus Over a year ago

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.

Collectives™ on Stack Overflow

How to execute sql query files via RPostgreSQL

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related