Diego Diego - 14 days ago 5
R Question

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.

Answer

You might want this,

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