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.
Schema is a collection of logical structures of data. In PostgreSQL, schema is a named collection of tables, views, functions, constraints, indexes, sequences etc. PostgreSQL supports having multiple schemas in a single database there by letting you namespace different features into different schemas.
If you write a database name, it must be the same as the database you are connected to. So to create a table in the new schema, use: CREATE TABLE myschema. mytable ( ... );
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'))
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With