1

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

Jindra Lacko
  • 7,814
  • 3
  • 22
  • 44
  • You may try the vector syntax which is specifically for RPostgresql: https://stackoverflow.com/a/12001451 Alternatively you can try DBI::Id(schema="my_schema", name="my_table"). – dracodoc Aug 13 '19 at 13:56

1 Answers1

0

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

edgararuiz
  • 625
  • 5
  • 9
  • Thanks, but this does not work: `copy_to()` creates a *temporary* table - and temporary tables in Postgres can not be used in a standard schema (they have their own, special scheme). Your code would translate to `create temporary table production.iris (...)` which is not a legit Postgres code. – Jindra Lacko Oct 26 '18 at 12:51