List tables within a Postgres schema using R

Question

I'm connecting to a PostgreSQL db using R and the RPostgreSQL package. The db has a number of schemas and I would like to know which tables are associated with a specific schema.

So far I have tried:

dbListTables(db, schema="sch2014")
dbGetQuery(db, "dt sch2014.*")
dbGetQuery(db, "\dt sch2014.*")
dbGetQuery(db, "\\dt sch2014.*")

None of which have worked.

This related question also exists: Setting the schema name in postgres using R, which would solve the problem by defining the schema at the connection. However, it's not yet been answered!

Answer 1

Reading this answer https://stackoverflow.com/a/15644435/2773500 helped. I can use the following to get the tables associated with a specific schema:

dbGetQuery(db,
           "SELECT table_name FROM information_schema.tables
                   WHERE table_schema='sch2014'")

Answer 2

The following should work (using DBI_v1.1.1)

DBI::dbListObjects(conn, DBI::Id(schema = 'schema_name'))

While it has all the info you want, it's hard to access and hard to read.

I would recommend something that produces a data frame:

# get a hard to read table given some Postgres connection `conn`
x = DBI::dbListObjects(conn, DBI::Id(schema = 'schema_name'))

# - extract column "table" comprising a list of Formal class 'Id' objects then
# - extract the 'name' slot for each S4 object element
# could also do `lapply(d$table, function(x) x@name)`
v = lapply(x$table, function(x) slot(x, 'name'))

# create a dataframe with header 'schema', 'table'
d = as.data.frame(do.call(rbind, v))

Or in one line:

d = as.data.frame(do.call(rbind, lapply(DBI::dbListObjects(conn, DBI::Id(schema = 'schema_name'))$table, function(x) slot(x, 'name'))))

Or in a more "tidy" way:

conn %>%
    DBI::dbListObjects(DBI::Id(schema = 'schema_name')) %>%
    dplyr::pull(table) %>%
    purrr::map(~slot(.x, 'name')) %>%
    dplyr::bind_rows()

OUTPUT is something like

> d
          schema     table
1    schema_name    mtcars