Filter multiple values ​​in postgreSQL database with dplyr

Question

I want to filter the postgres database, so I can cast a subset of the data in R for analysis. I can successfully filter by one condition (select one attribute), but not by a vector of values. For example, if I configured a connection to the database as such

library(dplyr) db <- src_postgres(dbname = 'conte_dev', host = '155.0.0.x', port = '1234', user = '...', password = '...') tbl_daymet <- tbl(db, 'daymet') 

then it works if i filter one value

  tbl_filtered <- tbl_daymet %>% dplyr::filter(featureid == 739554) tbl_filtered Source: postgres 9.3.5 [ conte@127.0.0.1 :5432/conte_dev] From: daymet [12,410 x 9] Filter: featureid == 739554 featureid date tmax tmin prcp dayl srad vp swe 1 739554 1980-01-18 -1.9375 -12.2500 0.000 32140.8 199.6 240 100.5 2 739554 1980-01-19 1.1250 -3.4375 0.000 32140.8 100.4 480 99.0 3 739554 1980-01-20 0.0000 -7.5000 0.000 32486.4 160.4 360 99.0 4 739554 1980-01-21 -6.5000 -15.7500 0.000 32486.4 193.6 180 99.0 5 739554 1980-01-22 -11.8125 -18.7500 0.000 32486.4 156.8 140 99.0 6 739554 1980-01-23 -6.4375 -16.5000 3.000 32832.0 157.2 160 102.5 7 739554 1980-01-24 -6.8750 -19.0000 3.125 32832.0 178.0 120 105.0 8 739554 1980-01-25 -15.0000 -23.0625 0.000 32918.4 184.4 80 105.0 9 739554 1980-01-26 -9.9375 -20.7500 0.000 33177.6 229.2 120 105.0 10 739554 1980-01-27 -7.0625 -15.9375 0.000 33177.6 202.4 165 105.0 .. ... ... ... ... ... ... ... ... ... 

However, if I try to filter a group of values ​​in featureid

 catches <- c(739554, 739554) tbl_derived_metrics <- tbl_daymet %>% dplyr::filter(featureid %in% catches) 

I get an error

Error in postgresqlExecStatement (conn, statement, ...): RS-DBI driver: (could not get the result: ERROR: syntax error or next to "739554" LINE 3: WHERE "featureid" IN 739554 ^) In addition: Warning message : In postgresqlQuickSQL (conn, statement, ...): Failed to create executeSELECT count (*) FROM (SELECT "featureid", "date", "tmax", "tmin", "prcp", "dayl", "srad "," vp "," swe "FROM" daymet "WHERE" featureid "IN 739554) AS" master "

I believe that this would work if it were a data frame in R, rather than a linked table in postgres. However, I need to do the filtering first, as the table contains several billion rows. Is there a special command I can use related to postgres? The current code does not work if I use characters or integers.

Answer 1

Using %in% in the filter function does not work if it is only one value, and not a vector with multiple values.

It works as a function with an ifelse operator for cases with 1 or more values.

 retreiveDaymet <- function(catchmentid, num.catch) { catches <- catchmentid[1:num.catch] if(num.catch == 1) { tbl_derived_metrics <- tbl_daymet %>% dplyr::filter(featureid == catches) } else { tbl_derived_metrics <- tbl_daymet %>% dplyr::filter(featureid %in% catches) } derived_metrics <- collect(tbl_derived_metrics) return(derived_metrics) } 

and then can be used as such

 catchment.numbers <- rep(c(1, 10, 50, 100, 200, 400, 800, 1000, 1500, 2000, 2500, 3000), each = 3) daymet.times <- data.frame(matrix(NA, length(catchment.numbers), 4)) for(i in 1:length(catchment.numbers)) { time1 <- system.time(foo <- retreiveDaymet(catchmentid = catchmentid, num.catch = catchment.numbers[i])) daymet.times[i, ] <- c(catchment.numbers[i], time1[1:3]) rm(foo) rm(time1) gc(verbose = FALSE) } names(daymet.times) <- c("num.catchments", names(system.time(1+1))[1:3]) 

This example is a little silly because foo thrown every time. It is just used for synchronization purposes. In the future, this code could add a function to do something with foo every time and add it to a frame or data list.