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 r-dbi/DBItest#340 and r-dbi/DBItest#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

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.