Setting the schema name in postgres using R

Question

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'?

you can run ALTER ROLE username SET search_path = schema1,schema2,schema3; server side... — Vao Tsun
– Vao Tsun, Commented Feb 9, 2017 at 15:30
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
– joop, Commented Feb 9, 2017 at 16:47
I use RPostgreSQL paackage. Is there a equivalent method for that package? — Sahil Doshi
– Sahil Doshi, Commented Feb 10, 2017 at 4: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
– Sahil Doshi, Commented Feb 10, 2017 at 5:13

Scarabee · Accepted Answer · 2018-03-05 12:39:30Z

21

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.

edited Mar 5, 2018 at 12:39

answered Mar 5, 2018 at 12:26

Scarabee

5,7545 gold badges32 silver badges59 bronze badges

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

2 Comments

DHW Over a year ago

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.

abalter Over a year ago

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, here, and here.

Collectives™ on Stack Overflow

Setting the schema name in postgres using R

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related