odbc
odbc copied to clipboard
Published
20 hours ago •
r-dbi
r-dbi
odbcListColumns fails for Materialized Views in PostgreSQL
trafficstars
Issue Description and Expected Result
Materialized Views in PostgreSQL show in the connections pane, but columns aren't displayed in the connections pane and no columns are returned by odbcListColumns.
Database
PostgreSQL 9.4.18
Reproducible Example
Given a PostgreSQL DB containing a table cars (the mtcars dataset from R) a Materialized View can be created in PostgreSQL via the following:
CREATE MATERIALIZED VIEW cars_mv AS SELECT mpg, hp, cyl FROM cars;
This view shows up in dbListTables:
dbListTables(con)
#> [1] "cars" "cars_mv"
And it also shows up in the connections pane:
However, no columns are shown in the expanded view of the connections pane and now columns are returned by odbcListColumns:
odbcListColumns(con, "cars_mv")
#> [1] name type
#> <0 rows> (or 0-length row.names)
Session Info
devtools::session_info()
#> ─ Session info ──────────────────────────────────────────────────────────
#> setting value
#> version R version 3.5.2 (2018-12-20)
#> os macOS Mojave 10.14.2
#> system x86_64, darwin15.6.0
#> ui X11
#> language (EN)
#> collate en_US.UTF-8
#> ctype en_US.UTF-8
#> tz America/Denver
#> date 2019-02-12
#>
#> ─ Packages ──────────────────────────────────────────────────────────────
#> package * version date lib source
#> assertthat 0.2.0 2017-04-11 [1] CRAN (R 3.5.0)
#> backports 1.1.3 2018-12-14 [1] CRAN (R 3.5.1)
#> bit 1.1-14 2018-05-29 [1] CRAN (R 3.5.0)
#> bit64 0.9-7 2017-05-08 [1] CRAN (R 3.5.0)
#> blob 1.1.1 2018-03-25 [1] CRAN (R 3.5.0)
#> callr 3.1.1 2018-12-21 [1] CRAN (R 3.5.0)
#> cli 1.0.1 2018-09-25 [1] CRAN (R 3.5.1)
#> crayon 1.3.4 2017-09-16 [1] CRAN (R 3.5.0)
#> DBI * 1.0.0.9001 2018-11-09 [1] Github (r-dbi/DBI@0b349d5)
#> desc 1.2.0 2019-01-25 [1] Github (r-lib/desc@42b9578)
#> devtools 2.0.1 2018-10-26 [1] CRAN (R 3.5.1)
#> digest 0.6.18 2018-10-10 [1] CRAN (R 3.5.0)
#> evaluate 0.12 2018-10-09 [1] CRAN (R 3.5.1)
#> fs 1.2.6 2018-08-23 [1] CRAN (R 3.5.1)
#> glue 1.3.0.9000 2019-01-28 [1] Github (tidyverse/glue@8188cea)
#> highr 0.7 2018-06-09 [1] CRAN (R 3.5.0)
#> hms 0.4.2 2018-03-10 [1] CRAN (R 3.5.0)
#> htmltools 0.3.6 2017-04-28 [1] CRAN (R 3.5.0)
#> knitr 1.21 2018-12-10 [1] CRAN (R 3.5.1)
#> magrittr 1.5 2014-11-22 [1] CRAN (R 3.5.0)
#> memoise 1.1.0 2017-04-21 [1] CRAN (R 3.5.0)
#> odbc * 1.1.6 2019-02-12 [1] local
#> pkgbuild 1.0.2 2018-10-16 [1] CRAN (R 3.5.0)
#> pkgconfig 2.0.2 2018-08-16 [1] CRAN (R 3.5.1)
#> pkgload 1.0.2 2018-10-29 [1] CRAN (R 3.5.1)
#> prettyunits 1.0.2 2015-07-13 [1] CRAN (R 3.5.0)
#> processx 3.2.1 2018-12-05 [1] CRAN (R 3.5.1)
#> ps 1.3.0 2018-12-21 [1] CRAN (R 3.5.0)
#> R6 2.3.0 2018-10-04 [1] CRAN (R 3.5.1)
#> Rcpp 1.0.0 2018-11-07 [1] CRAN (R 3.5.0)
#> remotes 2.0.2 2018-10-30 [1] CRAN (R 3.5.0)
#> rlang 0.3.1 2019-01-08 [1] CRAN (R 3.5.1)
#> rmarkdown 1.11 2018-12-08 [1] CRAN (R 3.5.0)
#> rprojroot 1.3-2 2018-01-03 [1] CRAN (R 3.5.0)
#> sessioninfo 1.1.1 2018-11-05 [1] CRAN (R 3.5.0)
#> stringi 1.2.4 2018-07-20 [1] CRAN (R 3.5.0)
#> stringr 1.4.0 2019-02-10 [1] CRAN (R 3.5.2)
#> testthat 2.0.1 2018-10-13 [1] CRAN (R 3.5.0)
#> usethis 1.4.0 2018-08-14 [1] CRAN (R 3.5.1)
#> withr 2.1.2.9000 2018-11-09 [1] Github (jimhester/withr@be57595)
#> xfun 0.4 2018-10-23 [1] CRAN (R 3.5.1)
#> yaml 2.2.0 2018-07-25 [1] CRAN (R 3.5.1)
#>
#> [1] /Library/Frameworks/R.framework/Versions/3.5/Resources/library
