DBI::dbCreateTable does not work as expected

[ablack3]

Expected result

DBI::dbCreateTable will create a new table in the database with the requested field names and datatypes.

Actual result: #> Error in fields[FALSE, ]: incorrect number of dimensions

library(DatabaseConnector)
connectionDetails <- createConnectionDetails(dbms = "postgresql",
                                             server = Sys.getenv("CDM5_POSTGRESQL_SERVER"),
                                             user = Sys.getenv("CDM5_POSTGRESQL_USER"),
                                             password = Sys.getenv("CDM5_POSTGRESQL_PASSWORD"))


con <- connect(connectionDetails)
#> Connecting using PostgreSQL driver

DBI::dbCreateTable(con,
                   name = "public.test_cohort_table",
                   fields = c(
                     cohort_definition_id = "INT",
                     subject_id = "INT",
                     cohort_start_date = "DATE",
                     cohort_end_date = "DATE"))
#> Error in fields[FALSE, ]: incorrect number of dimensions



disconnect(con)

sessionInfo()
#> R version 4.3.1 (2023-06-16)
#> Platform: aarch64-apple-darwin20 (64-bit)
#> Running under: macOS Sonoma 14.0
#> 
#> Matrix products: default
#> BLAS:   /Library/Frameworks/R.framework/Versions/4.3-arm64/Resources/lib/libRblas.0.dylib 
#> LAPACK: /Library/Frameworks/R.framework/Versions/4.3-arm64/Resources/lib/libRlapack.dylib;  LAPACK version 3.11.0
#> 
#> locale:
#> [1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8
#> 
#> time zone: Europe/Amsterdam
#> tzcode source: internal
#> 
#> attached base packages:
#> [1] stats     graphics  grDevices utils     datasets  methods   base     
#> 
#> other attached packages:
#> [1] DatabaseConnector_6.3.2
#> 
#> loaded via a namespace (and not attached):
#>  [1] bit_4.0.5         dplyr_1.1.4       compiler_4.3.1    tidyselect_1.2.0 
#>  [5] reprex_2.0.2      Andromeda_0.6.4   blob_1.2.4        yaml_2.3.7       
#>  [9] fastmap_1.1.1     R6_2.5.1          generics_0.1.3    knitr_1.44       
#> [13] tibble_3.2.1      R.cache_0.16.0    DBI_1.2.0         pillar_1.9.0     
#> [17] R.utils_2.12.2    rlang_1.1.2       utf8_1.2.4        cachem_1.0.8     
#> [21] xfun_0.40         fs_1.6.3          bit64_4.0.5       RSQLite_2.3.2    
#> [25] memoise_2.0.1     cli_3.6.2         withr_2.5.2       magrittr_2.0.3   
#> [29] digest_0.6.33     rstudioapi_0.15.0 rJava_1.0-6       hms_1.1.3        
#> [33] lifecycle_1.0.4   R.methodsS3_1.8.2 R.oo_1.25.0       vctrs_0.6.5      
#> [37] evaluate_0.23     glue_1.6.2        styler_1.10.2     fansi_1.0.6      
#> [41] rmarkdown_2.24    purrr_1.0.2       pkgconfig_2.0.3   tools_4.3.1      
#> [45] htmltools_0.5.6

^{Created on 2024-01-17 with reprex v2.0.2}

An example of how I think this should work:

con <- DBI::dbConnect(RPostgres::Postgres(),
                      dbname = Sys.getenv("CDM5_POSTGRESQL_DBNAME"),
                      host = Sys.getenv("CDM5_POSTGRESQL_HOST"),
                      user = Sys.getenv("CDM5_POSTGRESQL_USER"),
                      password = Sys.getenv("CDM5_POSTGRESQL_PASSWORD"))

DBI::dbCreateTable(con,
                   name = "test_cohort_table",
                   fields = c(
                     cohort_definition_id = "INT",
                     subject_id = "INT",
                     cohort_start_date = "DATE",
                     cohort_end_date = "DATE"))
#> Error: Failed to fetch row : ERROR:  relation "test_cohort_table" already exists

dplyr::tibble(DBI::dbGetQuery(con, "select * from test_cohort_table"))
#> # A tibble: 0 × 4
#> # ℹ 4 variables: cohort_definition_id <int>, subject_id <int>,
#> #   cohort_start_date <date>, cohort_end_date <date>

DBI::dbRemoveTable(con, "test_cohort_table")

DBI::dbDisconnect(con)

sessionInfo()
#> R version 4.3.1 (2023-06-16)
#> Platform: aarch64-apple-darwin20 (64-bit)
#> Running under: macOS Sonoma 14.0
#> 
#> Matrix products: default
#> BLAS:   /Library/Frameworks/R.framework/Versions/4.3-arm64/Resources/lib/libRblas.0.dylib 
#> LAPACK: /Library/Frameworks/R.framework/Versions/4.3-arm64/Resources/lib/libRlapack.dylib;  LAPACK version 3.11.0
#> 
#> locale:
#> [1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8
#> 
#> time zone: Europe/Amsterdam
#> tzcode source: internal
#> 
#> attached base packages:
#> [1] stats     graphics  grDevices utils     datasets  methods   base     
#> 
#> loaded via a namespace (and not attached):
#>  [1] vctrs_0.6.5       cli_3.6.2         knitr_1.44        rlang_1.1.2      
#>  [5] xfun_0.40         DBI_1.2.0         purrr_1.0.2       styler_1.10.2    
#>  [9] generics_0.1.3    glue_1.6.2        bit_4.0.5         htmltools_0.5.6  
#> [13] hms_1.1.3         fansi_1.0.6       rmarkdown_2.24    R.cache_0.16.0   
#> [17] tibble_3.2.1      evaluate_0.23     fastmap_1.1.1     yaml_2.3.7       
#> [21] lifecycle_1.0.4   compiler_4.3.1    dplyr_1.1.4       fs_1.6.3         
#> [25] blob_1.2.4        timechange_0.2.0  pkgconfig_2.0.3   rstudioapi_0.15.0
#> [29] R.oo_1.25.0       R.utils_2.12.2    digest_0.6.33     R6_2.5.1         
#> [33] tidyselect_1.2.0  utf8_1.2.4        reprex_2.0.2      pillar_1.9.0     
#> [37] magrittr_2.0.3    R.methodsS3_1.8.2 tools_4.3.1       withr_2.5.2      
#> [41] bit64_4.0.5       lubridate_1.9.2   RPostgres_1.4.6

^{Created on 2024-01-17 with reprex v2.0.2}