I cannot connect postgresql schema.table with dplyr package

dplyr, postgresql, r, rpostgresql

Solution

Finally dplyr has the solution to this problem thanks to the latest version 0.7 recently announced by Hadley Wickham. The DBI and dbplyr libraries greatly simplified the connection between dplyr and PostgreSQL.

con <- DBI::dbConnect(RPostgreSQL::PostgreSQL(), 
host = "database.rstudio.com",
user = "hadley",
password = rstudioapi::askForPassword("Database password"))
tbl <- dplyr::tbl(con, dbplyr::in_schema('mortalidad','def0307'))

Problem

Im trying to connect postgres with dplyr functions ``` my_db <- src_postgres(dbname = 'mdb1252', user = "diego", password = "pass") my_db src: postgres 9.2.5 [postgres@localhost:5432/mdb1252] tbls: alf, alturas, asociad, atenmed, base, bfa_boys_p_exp, bfa_boys_z_exp, bfa_girls_p_exp, bfa_girls_z_exp, bres, c21200012012, c212000392011, c212000532011, c21200062012, c212006222012, c212007352012, c212012112013, c212012242012, c212012452012, c2222012242012, calles, cap, cap0110, casos_tbc_tr09, casos_tbctr09, casosvadela, catpo, cbcvl, cie09, cie10, cie103d, cie103dantigua, cie10c, cie9a, cie9mc, clasiarc, coalc, coddepto, codedades, codest, codlocaerbio, codprov, coheb, cohec, cohep, cohiv, coho09_20110909_m, coign, combl, comet, comp, comport, conev, conymad, copri, corci3cod, corci910, cores, corin, cotab, cutoi, cutto, def0307,...... ``` but when I try to connect a tbl ``` my_tbl <- tbl(my_db, 'def0307') Error in postgresqlExecStatement(conn, statement, ...) : RS-DBI driver: (could not Retrieve the result : ERROR: no existe la relación «def0307» LINE 1: SELECT * FROM "def0307" WHERE 0=1; ^ ) ``` I think the problem is a schema issue because sql should be: ``` SELECT * FROM mortalidad.def0307 ``` I made `my_tbl` <- `tbl(my_db, 'mortalidad.def0307');` `my_tbl <- tbl(my_db, c('mortalidad','def0307'))` without a solution. Im having a lot of fun working with dplyr Im from SQL but I wish resolve that and trying dplyr skills. Thanks in advance.

Original source