r-dbi
Schema with DBI::Id appears not to work with `dbListTables`
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.
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
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.
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
#>
#> ──────────────────────────────────────────────────────────────────────────────
Yeah, dbListObjects() can't work with unnamed Id() . The function will still return named "Id" objects that can be consumed.