2

what is the difference between src_postgres and dbConnect function? Both can be used to connect R with postgres using the RPosgresql package. In my experiments I only could use src_postgres to read and dbConnect to write to the database.

When I tried it in different combinations I only received errors.

This seems fairly strange to me.

Georg Heiler
  • 16,916
  • 36
  • 162
  • 292

1 Answers1

3

src_postgres is a function for creating a connection to a PostgreSQL database from the dplyr package. The RPostgreSQL package implements a method for the generic dbConnect from the DBI package. src_postgres calls dbConnect from RPostgreSQL (I assume).

The generic connection object returned by dbConnect is meant to be an open ended interface for sending SQL queries to the data base. This means you could feed it any select, update, insert, delete, etc. query that you like.

src_postgres is part of the higher level interface to working with data from databases that Hadley built in dplyr. The src_* functions connect to a db and then the tbl functions specify a more specific data source (table, view, arbitrary select query) to pull data from. There are some basic table manipulation functions in dplyr but I don't believe it is intended to be a tool for doing update or insert type things in the db. That's just not what that tool is for. Note that the "verbs" implemented in dplyr are all focused on pulling data out and summarising (select, filter, mutate, etc.).

If you need to alter data in a data base on a row level, you'll need to send SQL queries to a connection created by dbConnect. If all you're doing is pulling data from a db and analyzing it in R, that is what dplyr is for.

joran
  • 169,992
  • 32
  • 429
  • 468
  • So if I first need to read some data and then perform a calculation and later write the result to the DB you would suggest to ONLY use dbConnect() ? – Georg Heiler Jun 09 '15 at 21:42
  • @geoHeil You could do that, but there's nothing stopping you from using **dplyr** to gather and manipulate and then updating via a connection from `dbConnect`. – joran Jun 09 '15 at 21:46
  • so there is no recommended or best practice approach? – Georg Heiler Jun 09 '15 at 21:50
  • @geoHeil That would depend strongly on one's preferences regarding tools (do you _like_ **dplyr**? do you _hate_ it?), your preferences on coding style (is what you're doing able to be done more cleanly, concisely, more maintainable using one or the other?). And only you can answer those questions. – joran Jun 09 '15 at 21:53