13

I am using R to connect to a PostgreSQL database. Connection is done using below function:

dbConnect(m, dbname=dbname, host=host, port=port, user=user, password=password)

m is the driver (postgres).

But it does not allow me to set a particular schema name under which my tables are. How do I connect to a particular schema so that every time I don't have to prefix schema name to the table names?

Is there an equivalent statement in R for set search_path = 'myschema'?

Scarabee
  • 5,437
  • 5
  • 29
  • 55
Sahil Doshi
  • 621
  • 1
  • 9
  • 15
  • you can run `ALTER ROLE username SET search_path = schema1,schema2,schema3;` server side... – Vao Tsun Feb 09 '17 at 15:30
  • 1
    I don't know which package you use. in RODBC you can do `library (RODBC)` plus `co <- odbcConnect( 'thedata' )` plus `odbcQuery (co, 'SET search_path = myschema;')` – joop Feb 09 '17 at 16:47
  • I use RPostgreSQL paackage. Is there a equivalent method for that package? – Sahil Doshi Feb 10 '17 at 04:30
  • @VaoTsun ALTER ROLE username SET search_path = schema1,schema2,schema3; does this mean, by default all my searching for tables and creating new tables will happen in schema1 ? And if I explicitely write : set search_path = schema2; then it will do its operations under schema2(being schema1 the default schema) ? – Sahil Doshi Feb 10 '17 at 05:13

1 Answers1

18

You can use:

dbConnect(
  m,
  dbname=dbname,
  host=host,
  port=port,
  user=user,
  password=password,
  options="-c search_path=myschema"
)

It works with RPostgreSQL and also with RPostgres.

Scarabee
  • 5,437
  • 5
  • 29
  • 55
  • 2
    For RPostgres users: this definitely looks to me like a flaw in the dbConnect documentation. This is only working for me exactly as it is above, and not without the `options=` argument name, even though the docs put this as going into the `...`. The description of `...` is for "Other name-value pairs", and it links you to the PostgreSQL docs for command-line options, but that would obviously lead you to believe that `search_path = "myschema"` is the intended syntax. Basically, `options` is a required arg. even though it's not listed, and R name-value pairs don't really work at all. – DHW Apr 12 '20 at 15:59
  • In my experience, this method does not always honor the schema. I believe the more rigorous method any more is to use the `Id()` construct. For example, `dbExistTable(con, Id(schema="sss"))` See [here](https://rdrr.io/cran/DBI/man/Id.html), [here](https://rpostgres.r-dbi.org/reference/postgres-tables.html), and [here](https://dbi.r-dbi.org/reference/id). – abalter Mar 22 '23 at 05:44