Ignoring case with remote database table filter operations

[geotheory]

I've detailed this question on this SO page.

I can't figure out how to apply a non-case-sensitive filter query to a remote PostgreSQL table using dplyr. To demonstrate:

require(dplyr)
require(stringr)
require(RPostgreSQL)

drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, dbname="mydb", host="localhost", port=5432, user="username")

# create db table
copy_to(con, iris, "iris", temporary = FALSE)

# dplyr remote database table
iris_pg <- tbl(con, "iris")

iris_pg %>% filter(str_detect(Species, 'setosa')) %>% head(3) %>% collect()
# A tibble: 3 x 5
  Sepal.Length Sepal.Width Petal.Length Petal.Width Species
*        <dbl>       <dbl>        <dbl>       <dbl> <chr>  
1          5.1         3.5          1.4         0.2 setosa 
2          4.9         3            1.4         0.2 setosa 
3          4.7         3.2          1.3         0.2 setosa

iris_pg %>% filter(str_detect(Species, 'Setosa')) %>% head(3) %>% collect()
# A tibble: 0 x 0

To ignore case stringr::fixed('Setosa', ignore_case=TRUE) works with tibble filtering. But with the postgres table it has no effect:

iris_pg %>% filter(str_detect(Species, stringr::fixed('SETOSA', ignore_case=TRUE))) %>% head(3) %>% collect()
# A tibble: 0 x 0

Is there any way around this, or possibly dev plans to accommodate the ignore_case arguments or fixed() or regex() for database table filtering?

[hadley]

You will need to generate the needed SQL by hand (i.e. relying on the SQL fall back translations). There's unfortunately no way that dbplyr can translate every stringr feature into every database backend.

[geotheory]

Fair enough, thanks Hadley. I'll explore the suggestions received in SO.

[romainfrancois]

Perhaps this should be closed, for now I'll just move it to dbplyr

[ghost]

This issue was moved by romainfrancois to tidyverse/dbplyr#150.

[ghost]

This issue was moved by romainfrancois to tidyverse/dbplyr#151.

[lock]

This old issue has been automatically locked. If you believe you have found a related problem, please file a new issue (with reprex) and link to this issue. https://reprex.tidyverse.org/