dbplyr in_schema() function behaving strangely

Question

I am using the in_schema() function from dbplyr package to create a table in a named schema of a postgresql database from R.

It is not a new piece of code and it used to work as expected = creating a table called 'my_table' in schema 'my_schema'.

con <- dbConnect(odbc::odbc(), 
                 driver = "PostgreSQL Unicode", 
                 server = "server", 
                 port = 5432, 
                 uid = "user name", 
                 password = "password", 
                 database = "dbase")

dbWriteTable(con,
             in_schema('my_schema', 'my_table'),
             value = whatever) # assume that 'whatever' is a data frame...

This piece of code has now developed issues and unexpectedly started to create a table called 'my_scheme.my_table' in the default public scheme of my database, instead of the expected my_schema.my_table.

Has anybody else noticed such behaviour, and is there a solution (except using the default postgresql scheme, which is not practical in my case)?

You may try the vector syntax which is specifically for RPostgresql: stackoverflow.com/a/12001451 Alternatively you can try DBI::Id(schema="my_schema", name="my_table"). — dracodoc

edgararuiz edgararuiz · Accepted Answer · 2018-09-22T21:22:11

for that, I would recommend using copy_to() instead of dbWriteTable(): copy_to(con, iris, in_schema("production", "iris"))

dbplyr in_schema() function behaving strangely

1 Answers