I cannot connect postgresql schema.table with dplyr package

Question

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.

I vaguely remember thinking about this. Does `tbl(my_db, sql('mortalidad.def0307'))` work? — hadley, Feb 06 '14 at 15:18
Thanks for responding quickly. I tried with the options you refer me but the error continues. Now is with both options .... Error in postgresqlExecStatement(conn, statement, ...) : RS-DBI driver: (could not Retrieve the result : ERROR: error de sintaxis en o cerca de «)» LINE 1: SELECT * FROM (mortalidad.def0307) AS "_W2" WHERE 0=1; ^ ) — Diego, Feb 06 '14 at 17:04
@hadley thinking about your answer I came up to write the full sentence `my_tbl = tbl(my_db, dplyr::sql('SELECT * FROM mortalidad.def0307'))` and there I could connect to the schema and table. Thanks a lot. — Diego, Feb 06 '14 at 22:27

score 14 · Accepted Answer · answered Jun 27 '17 at 19:34

14

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'))

answered Jun 27 '17 at 19:34

Diego

2,196
1
21
26

2

In case someone comes here, an alternate is that I had to include the schema in double quotes, so this variant worked for me: `tbl <- dplyr::tbl(con, dbplyr::in_schema('"mortalidad"','def0307'))` – mpettis Jan 17 '18 at 01:50
Thanks @Diego. I wonder where the documentation for this topic lives? – Dan Jan 30 '18 at 23:29

score 9 · Answer 2 · edited Sep 27 '14 at 14:27

9

You might want this,

db=src_postgres(dbname = 'mdb1252',  
               user = "diego", password = "pass", options="-c search_path=mortalidad")

edited Sep 27 '14 at 14:27

Luís Cruz

14,780
16
68
100

answered Sep 27 '14 at 13:45

ph49

91
1
2

That works!. I would like just `my_tbl <- tbl(my_db, 'mortalidad.def0307')`. I think that @hadley is working in that enhancement. Thanks a lot – Diego Feb 09 '15 at 13:16

score 4 · Answer 3 · answered Nov 21 '16 at 16:06

If anybody ends up here with the same problem, here is what works for me: (taken from @Diego's comment from Feb 6'14)

postgre_table <- function (src, schema, table) {
  paste('SELECT * FROM', paste(schema, table, sep = '.')) %>% 
    sql() %>% tbl(src = src)
}

I cannot connect postgresql schema.table with dplyr package

3 Answers3

Linked