29

I'm using RPostgreSQL to read and write data. Reading from any schema works perfectly, but I'm not able to write to non-public schemas. For example, the following code places a table in the public schema, with the name myschema.tablex

# write dataframe to postgres 
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, host="localhost", user="postgres", password="zzzz", dbname="mydatabase", port="5436")
if(dbExistsTable(con,"myschema.tablex")) {
  dbRemoveTable(con,"myschema.vkt_tablex")}
dbWriteTable(con,"myschema.tablex", dataframe, row.names=F)

What I want to do, is to place the table tablex in the schema myschema. I've also tried to name the schema in the connection: dbname="mydatabase.myschema" and trying the argument schemaname which I saw referred to in an earlier bug.

None of these approaches work, so I'm wondering if there is another method that I can use.

djq
  • 14,810
  • 45
  • 122
  • 157

3 Answers3

56

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

Tomas Greif
  • 21,685
  • 23
  • 106
  • 155
6

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:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thanks @Erwin. If I have five schemas do I need to list them all in my preferred order, or just the one I want `R` to use? – djq Apr 05 '12 at 17:41
  • @celenius: You don't have to list them all. The first schema in your `search_path` that your current role can access will be used for *object creation*. All of them will be *searched* in order for objects until they are found. Ohter schemas are invisible. Special rules apply for system schemas. Much like a search path in a file system. The manual tells it all. – Erwin Brandstetter Apr 05 '12 at 17:50
  • Note however that schema prefixing a tablename on create should work as well. i.e.: create schema test; create table test.abc (i int); \d test.abc – Scott Marlowe Apr 06 '12 at 16:21
  • @celenius: Like Scott said: the search_path only provides a default. Schema-qualifying names always overrules the default. Much like a fully qualified path in a file system. – Erwin Brandstetter Apr 24 '12 at 19:27
  • You could put @ErwinBrandstetter solution in a transaction, e.g., `RPostgres::dbWithTransaction`, so setting the search path is part of your transaction, afterwards it returns back to your ordinary search path. – Matt Bannert Oct 15 '19 at 14:52
3

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)

Arthur Yip
  • 5,810
  • 2
  • 31
  • 50
  • See `help(Id)` in the DBI package, the Id class refers "to a table nested in a hierarchy (e.g. within a schema)". This can also be used as the name argument in `dbWriteTable`. – Paul Rougieux Dec 01 '20 at 13:07