CODEWITHSUNDEEP
rpostgresqlrpostgresqldplyr
Diego
Diego

Reputation: 2356

I cannot connect postgresql schema.table with dplyr package

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.

Upvotes: 19

Views: 3513

Answers (3)

Diego
Diego

Reputation: 2356

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

Upvotes: 14

Jan Kislinger
Jan Kislinger

Reputation: 1573

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

Upvotes: 4

ph49
ph49

Reputation: 91

You might want this,

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

Upvotes: 9

Related Questions