2

I'm unable to connect to a PostgreSQL table (amplitude analytics) using this dplyr approach:

dplyr::tbl(amplitude_db, from = "app123.myEvent") %>% show_query()

Output:

 RS-DBI driver: (could not Retrieve the result : ERROR:  relation "app123.myEvent" does not exist

However, this approach works:

dplyr::tbl(myCon,sql(("SELECT * FROM app123.myEvent"))) %>% show_query()

Output: 

<SQL>
SELECT * FROM app123.myEvent
>

Any ideas why the first approach is failing?

Mogsdad
  • 44,709
  • 21
  • 151
  • 275
user2579689
  • 97
  • 6

2 Answers2

2

I think your first approach can work if you use in_schema from package dbplyr:

tbl(myCon, in_schema("app123", "myEvent")) %>% show_query()

If you know you're going to query multiple tables from the same schema app123, it can be a good idea to set the search path to this schema:

dbSendQuery(myCon, "set search_path to app123")

(this way you won't need to specify the schema each time)

Scarabee
  • 5,437
  • 5
  • 29
  • 55
0

Amplitude support pointed me here, it's a known issue: https://github.com/rstats-db/RPostgres/issues/32

user2579689
  • 97
  • 6