我不能连接postgresql schema.table与dplyr包 [英] I cannot connect postgresql schema.table with dplyr package

问题描述

我试图连接postgres与dplyr函数

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,......

但是当我尝试conne ct a tbl

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;
                      ^
)

我认为问题是模式问题，因为sql应该是：

I think the problem is a schema issue because sql should be:

 SELECT * FROM mortalidad.def0307

我做了 my_tbl < - c $ c> tbl（my_db，'mortalidad.def0307'）;

my_tbl< - tbl（my_db，c（'mortalidad'，'def0307'））没有解决方案。

我有很多乐趣使用dplyr Im从SQL，但我希望解决和尝试dplyr技能。

Im having a lot of fun working with dplyr Im from SQL but I wish resolve that and trying dplyr skills.

提前感谢

推荐答案

最后，dplyr有解决这个问题的方法，感谢最新版本0.7最近由Hadley Wickham宣布。 DBI和dbplyr库大大简化了dplyr和PostgreSQL之间的连接。

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

这篇关于我不能连接postgresql schema.table与dplyr包的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！