R access redshift table under a schema with dplyr or RPostgreSQL

Question

I'm trying to connect to a database in Redshift with my mac.
I managed to connect to Redshift with both dplyr and RPostgreSQL, but even though i can see all the available tables regardless of schema, i'm unable to access any of them as they all are under diiferent schemas.
I've tried all sorts of syntax to specify the schema but i'm not getting anywhere.

Here's my RPostgreSQL code:

library(RPostgreSQL)
drv <- dbDriver("PostgreSQL")
postgre.conn <-dbConnect(drv, 
    host="localhost", port="XXXX", dbname="redshiftdb", 
    user="XXXX", password="XXXX")
dbListTables(postgre.conn)

This lists all the tables regardless of schema.

I can see all the tables under a specific schema so this works:

dbGetQuery(postgre.conn,
       "SELECT table_name FROM information_schema.tables
       WHERE table_schema='my_schema'") 

but i'm then unable to access data from my_schema.my_table with any of these commands:

dbSendQuery(postgre.conn,"SELECT * FROM my_table LIMIT 10")
dbSendQuery(postgre.conn,"SELECT * FROM my_schema.my_table LIMIT 10")
dbSendQuery(postgre.conn,"SELECT * FROM my_table WHERE table_schema='my_schema' LIMIT 10")
dbSendQuery(postgre.conn,"SELECT * FROM c("my_schema", "my_table") LIMIT 10")

Similarly here's my dplyr code:

library(dplyr)
dplyr.conn <- src_postgres(host="localhost", port="XXXX", 
    dbname = "redshiftdb", user = "XXXX", password = "XXXX") 
head(src_tbls(dplyr.conn)) # lists all the tables, regardless of schema

But then, none of these work:

tbl(dplyr.conn, sql("SELECT * FROM my_table LIMIT 10"))
tbl(dplyr.conn, sql("SELECT * FROM my_schema.my_table LIMIT 10"))

and i also tried specifying the search path in both cases as such:

dplyr.conn <- src_postgres(host="localhost", port="XXXX", 
                  dbname = "redshiftdb", user = "XXXX", password = "XXXX", 
                  options="-c search_path=my_schema") 

postgre.conn <-dbConnect(drv,
             host="localhost",
             port="XXXX", 
             dbname="redshiftdb",
             user="XXXX",
             password="XXXX",
             options="-c search_path=my_schema")

but these still didn't work:

tbl(dplyr.conn, sql("SELECT * FROM my_table LIMIT 10"))
dbSendQuery(postgre.conn,"SELECT * FROM my_table LIMIT 10")

any ideas...?

Answer 1

Use the in_schema() command. The code would be something like:

t <- tbl(dplyr.conn, in_schema("sheman_name", "table_name")