Writing to specific schemas with RPostgreSQL

Use this:

library(RPostgreSQL)
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, dbname = "db", host = "host", port = 5432,
                 user = "user", password = "pwd")
dbWriteTable(con, c("yourschema", "yourtable"), value = yourRdataframe)
dbDisconnect(con)

More details: https://stat.ethz.ch/pipermail/r-sig-db/2011q1/001043.html

In case a reader is using the newer package RPostgres to do this, the code to specify schemas is:

dbCreateTable(conn = con, name = Id(schema = "yourschema", table = "yourtable"), fields = yourRdataframe)

The default schema where objects are created is defined by the search_path. One way would be to set it accordingly. For instance:

SET search_path = myschema, public;

I quote the manual:

When objects are created without specifying a particular target schema, they will be placed in the first schema listed in the search path. An error is reported if the search path is empty.

You can also make this the default for a role, so it is set automatically for every connection made by this role. More:

• How does the search_path influence identifier resolution and the "current schema"