Schema with DBI::Id appears not to work with `dbListTables`

[abalter]

I'm using the RPostgres::Postgres() driver. I can't get DBI to respect schema without using SQL. These two give different results:

dbListTables(con, Id(schema='prp049'))

dbGetQuery(con, "SELECT table_name FROM information_schema.tables WHERE table_schema='prp049'")

I don't know what I could do to make a reprex here.

[krlmlr]

Thanks. Can you use dbListObjects() ?

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
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}

[krlmlr]

This might be broken still, or again. We switched to unnamed components in Id(), tracking in https://github.com/r-dbi/DBItest/issues/340 and https://github.com/r-dbi/DBItest/issues/367.

[dpprdan]

This might be broken still, or again.

FWIW dbListObjects() works with named Id() as prefix, but does not (i.e. returns an empty result!) with an unnamed Id().

library(RPostgres)
con <- dbConnect(Postgres())
dbExecute(con, "create schema if not exists test;")
#> [1] 0
dbWriteTable(con, Id(schema = "test", table = "iris"), iris, overwrite = TRUE)
dbListObjects(con, Id(schema = "test"))
#>                table is_prefix
#> 1 <Id> "test"."iris"     FALSE
dbListObjects(con, Id("test"))
#> [1] table     is_prefix
#> <0 rows> (or 0-length row.names)
dbExecute(con, "drop schema test cascade")
#> NOTICE:  drop cascades to table test.iris
#> [1] 0
dbDisconnect(con)

BTW This is the same for RMariaDB.

Session info

``` r sessioninfo::session_info() #> ─ Session info ─────────────────────────────────────────────────────────────── #> setting value #> version R version 4.3.3 (2024-02-29 ucrt) #> os Windows 10 x64 (build 19045) #> system x86_64, mingw32 #> ui RTerm #> language EN #> collate German_Germany.utf8 #> ctype German_Germany.utf8 #> tz Europe/Berlin #> date 2024-04-19 #> pandoc 3.1.1 @ C:/Program Files/RStudio/resources/app/bin/quarto/bin/tools/ (via rmarkdown) #> #> ─ Packages ─────────────────────────────────────────────────────────────────── #> package * version date (UTC) lib source #> bit 4.0.5 2022-11-15 [1] CRAN (R 4.3.1) #> bit64 4.0.5 2020-08-30 [1] CRAN (R 4.3.1) #> blob 1.2.4 2023-03-17 [1] CRAN (R 4.3.3) #> cli 3.6.2 2023-12-11 [1] CRAN (R 4.3.3) #> DBI 1.2.2 2024-02-16 [1] CRAN (R 4.3.3) #> digest 0.6.35 2024-03-11 [1] CRAN (R 4.3.3) #> evaluate 0.23 2023-11-01 [1] CRAN (R 4.3.3) #> fastmap 1.1.1 2023-02-24 [1] CRAN (R 4.3.3) #> fs 1.6.3 2023-07-20 [1] CRAN (R 4.3.3) #> generics 0.1.3 2022-07-05 [1] CRAN (R 4.3.1) #> glue 1.7.0 2024-01-09 [1] CRAN (R 4.3.3) #> hms 1.1.3 2023-03-21 [1] CRAN (R 4.3.1) #> htmltools 0.5.8.1 2024-04-04 [1] CRAN (R 4.3.3) #> knitr 1.46 2024-04-06 [1] CRAN (R 4.3.3) #> lifecycle 1.0.4 2023-11-07 [1] CRAN (R 4.3.3) #> lubridate 1.9.3 2023-09-27 [1] CRAN (R 4.3.3) #> magrittr 2.0.3 2022-03-30 [1] CRAN (R 4.3.1) #> pkgconfig 2.0.3 2019-09-22 [1] CRAN (R 4.3.1) #> purrr 1.0.2 2023-08-10 [1] CRAN (R 4.3.1) #> R.cache 0.16.0 2022-07-21 [1] CRAN (R 4.3.3) #> R.methodsS3 1.8.2 2022-06-13 [1] CRAN (R 4.3.3) #> R.oo 1.26.0 2024-01-24 [1] CRAN (R 4.3.3) #> R.utils 2.12.3 2023-11-18 [1] CRAN (R 4.3.3) #> reprex 2.1.0 2024-01-11 [1] CRAN (R 4.3.3) #> rlang 1.1.3 2024-01-10 [1] CRAN (R 4.3.3) #> rmarkdown 2.26 2024-03-05 [1] CRAN (R 4.3.3) #> RPostgres * 1.4.6.9006 2024-04-15 [1] Github (r-dbi/RPostgres@27b279e) #> rstudioapi 0.16.0 2024-03-24 [1] CRAN (R 4.3.3) #> sessioninfo 1.2.2 2021-12-06 [1] CRAN (R 4.3.3) #> styler 1.10.3 2024-04-07 [1] CRAN (R 4.3.3) #> timechange 0.3.0 2024-01-18 [1] CRAN (R 4.3.3) #> vctrs 0.6.5 2023-12-01 [1] CRAN (R 4.3.3) #> withr 3.0.0 2024-01-16 [1] CRAN (R 4.3.3) #> xfun 0.43 2024-03-25 [1] CRAN (R 4.3.3) #> yaml 2.3.8 2023-12-11 [1] CRAN (R 4.3.2) #> #> [1] C:/Users/Daniel/AppData/Local/R/win-library/4.3 #> [2] C:/Program Files/R/R-4.3.3/library #> #> ────────────────────────────────────────────────────────────────────────────── ```

[krlmlr]

Yeah, dbListObjects() can't work with unnamed Id() . The function will still return named "Id" objects that can be consumed.