dbExistsTable does not honor case of table name

[r2evans]

Problem:

dbExistsTable is inconsistent with regards to case of table names.

Expected

Because postgres down-cases identifiers by default (the use of dbQuoteIdentifier is the exception), I expect that queries for unquoted mixed-case table names to still compare successfully with their down-cased name in the database.

Actual

Unquoted, I expect all searches for mixed-case table names to match the lower-case:

tblnm <- "foobar"
dbExecute(connpg, sprintf("create table %s (id serial8 primary key, num integer)", tblnm))
# [1] 0
dbExistsTable(connpg, "foobar") # correct
# [1] TRUE
dbExistsTable(connpg, "Foobar") # incorrect
# [1] FALSE
dbExistsTable(connpg, dbQuoteIdentifier(connpg, tblnm)) # incorrect
# [1] FALSE
dbExecute(connpg, sprintf('drop table if exists %s', tblnm))
# [1] 0

Unquoted, down-cased within postgres:

tblnm <- "Foobar"
dbExecute(connpg, sprintf("create table %s (id serial8 primary key, num integer)", tblnm))
# [1] 0
dbExistsTable(connpg, "foobar") # correct
# [1] TRUE
dbExistsTable(connpg, "Foobar") # incorrect
# [1] FALSE
dbExistsTable(connpg, dbQuoteIdentifier(connpg, tblnm)) # correct
# [1] FALSE
dbExecute(connpg, sprintf('drop table if exists %s', tblnm))
# [1] 0

Quoted on definition, this should be the most-specific:

tblnm <- dbQuoteIdentifier(connpg, "Foobar")
dbExecute(connpg, sprintf("create table %s (id serial8 primary key, num integer)", tblnm))
# [1] 0
dbExistsTable(connpg, "foobar") # correct
# [1] FALSE
dbExistsTable(connpg, "Foobar") # incorrect
# [1] TRUE
dbExistsTable(connpg, tblnm) # incorrect
# [1] FALSE
dbExecute(connpg, sprintf('drop table if exists %s', tblnm))
# [1] 0

Session info:

devtools::session_info()
# Session info --------------------------------------------------------------------------------------------------------------------
#  setting  value                       
#  version  R version 3.3.1 (2016-06-21)
#  system   x86_64, mingw32             
#  ui       RTerm                       
#  language (EN)                        
#  collate  English_United States.1252  
#  tz       America/Los_Angeles         
#  date     2016-09-09                  
# Packages ------------------------------------------------------------------------------------------------------------------------
#  package        * version    date       source                                     
#  colorspace       1.2-6      2015-03-11 CRAN (R 3.3.0)                             
#  crayon           1.3.2      2016-06-28 CRAN (R 3.3.1)                             
#  DBI            * 0.5        2016-09-06 Github (rstats-db/DBI@bc730b9)             
#  devtools         1.12.0     2016-06-24 CRAN (R 3.3.1)                             
#  digest           0.6.10     2016-08-02 CRAN (R 3.3.1)                             
#  dplyr          * 0.5.0.9000 2016-08-26 Github (javierluraschi/dplyr@6220be8)      
#  evaluate         0.9        2016-04-29 CRAN (R 3.3.0)                             
#  jsonlite         1.0        2016-07-01 CRAN (R 3.3.1)                             
#  magrittr         1.5        2014-11-22 CRAN (R 3.3.0)                             
#  memoise          1.0.0      2016-01-29 CRAN (R 3.3.0)                             
#  purrr            0.2.2      2016-06-18 CRAN (R 3.3.1)                             
#  R6               2.1.3      2016-08-19 CRAN (R 3.3.1)                             
#  Rcpp             0.12.7     2016-09-05 CRAN (R 3.3.1)                             
#  readr          * 1.0.0      2016-08-03 CRAN (R 3.3.1)                             
#  rJava            0.9-8      2016-01-07 CRAN (R 3.3.0)                             
#  roxygen2         5.0.1      2015-11-11 CRAN (R 3.3.0)                             
#  RPostgres      * 0.1-2      2016-09-06 local (rstats-db/RPostgres@NA)             
#  rredis         * 1.7.1      2016-09-07 Github (bwlewis/rredis@334643c)            
#  RSQLite        * 1.0.9007   2016-09-06 local (rstats-db/RSQLite@NA)               
#  RSQLServer     * 0.2.099    2016-08-29 Github (imanuelcostigan/RSQLServer@1730a84)
#  rvest          * 0.3.2      2016-06-17 CRAN (R 3.3.1)                             
#  stringi          1.1.1      2016-05-27 CRAN (R 3.3.0)                             
#  stringr          1.1.0      2016-08-19 CRAN (R 3.3.1)                             
#  testthat         1.0.2.9000 2016-05-16 Github (hadley/testthat@4b61621)           
#  tibble           1.1        2016-07-04 CRAN (R 3.3.1)                             
#  tidyr          * 0.6.0      2016-08-12 CRAN (R 3.3.1)                             
#  withr            1.0.2      2016-06-20 CRAN (R 3.3.1)                             
#  xml2           * 1.0.0      2016-06-24 CRAN (R 3.3.1)                             

[r2evans]

Same think with dbWriteTable, should I include it as a separate issue?

[krlmlr]

Thanks. The dbQuoteIdentifier() method doesn't currently indicate that the identifier is to be treated as case-sensitive. We should rather require case-sensitivity for the DBI specs, and also test it.

[github-actions]

This old thread has been automatically locked. If you think you have found something related to this, please open a new issue and link to this old issue if necessary.