Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

List tables in schema with dbListTables #399

Open
ablack3 opened this issue Jul 8, 2022 · 3 comments
Open

List tables in schema with dbListTables #399

ablack3 opened this issue Jul 8, 2022 · 3 comments

Comments

@ablack3
Copy link

ablack3 commented Jul 8, 2022

I would like to list tables in a specific schema using dbListTables and use syntax that will work with other DBI backends that support listing tables in a schema (e.g. odbc).

library(DBI)
con <- DBI::dbConnect(RPostgres::Postgres(),
                      host     = "localhost",
                      dbname   = "cdm",
                      user     = "postgres",
                      password = "",
                      port     = 5432)

dbIsValid(con)
#> [1] TRUE

dbExecute(con, "create schema test;")
#> [1] 0
dbWriteTable(con, Id(schema = "test", table = "iris"), iris)
dbListTables(con)
#> character(0)
dbListTables(con, schema_name = "test")
#> character(0)

dbExecute(con, "SET search_path TO test")
#> [1] 0
dbListTables(con)
#> [1] "iris"


dbExecute(con, "drop schema test cascade")
#> NOTICE:  drop cascades to table iris
#> [1] 0
dbDisconnect(con)

Created on 2022-07-08 by the reprex package (v2.0.1)

dbListTables_PqConnection <- function(conn, ...) {

@plpxsk
Copy link

plpxsk commented Nov 1, 2022

Here is one way

In DBI::dbConnect(), pass the following option:

options = "-c search_path=myschema"

Or, to specify schema elsewhere:

options = paste0("-c search_path=", schema)

Then, listing and writing tables should work directly on this connection

Source: https://stackoverflow.com/a/49110504/3217870

EDIT: minor edits

@plpxsk
Copy link

plpxsk commented Nov 1, 2022

An alternative is to list the "objects" (tables) in the schema:

dbListObjects(con, DBI::Id(schema = schema))

Via https://stackoverflow.com/a/66717542/3217870

@krlmlr
Copy link
Member

krlmlr commented Apr 1, 2023

Thanks.

The options argument is documented in https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS which is linked from https://rpostgres.r-dbi.org/reference/postgres#arguments-1. It's very powerful indeed.

How can we improve the documentation to make this information easier to access?

library(DBI)
con <- DBI::dbConnect(RPostgres::Postgres())

dbExecute(con, "create schema if not exists test;")
#> [1] 0
dbWriteTable(con, Id(schema = "test", table = "iris"), iris, overwrite = TRUE)
dbListObjects(con, DBI::Id(schema = "test"))
#>                              table is_prefix
#> 1 <Id> schema = test, table = iris     FALSE

con2 <- DBI::dbConnect(RPostgres::Postgres(), options = "-c search_path=test")
dbListTables(con2)
#> [1] "iris"
dbDisconnect(con2)


dbExecute(con, "drop schema test cascade")
#> NOTICE:  drop cascades to table test.iris
#> [1] 0
dbDisconnect(con)

Created on 2023-04-01 with reprex v2.0.2

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

3 participants