Postgresql Options for Mac OS X
On May 14, 2009, at 12:13 AM, Prasenjit Kapat wrote:
Hi, On Mon, May 11, 2009 at 6:18 PM, Neil Tiffin <neilt at neiltiffin.com> wrote:
In trying to figure out how the best way to access a postgresql database from R on Mac OS X. Currently my database has 88 million rows (9 GB) and is growing so I am looking for a stable interface. The following options seem to be available: 1. RdbiPgSQL_1.18.1 2. RPostgreSQL_0.1-4 3. TSPostgreSQL_2009.3-2 These packages seemed to be obsolete and no longer supported/ recommended: 4. RPgSQL 1.0 5. Rdbi 0.1.2 Results ====== #1 relies on #5 and has an older connection syntax. #2 appears relatively new and has not reached 1.0 status yet. For someone used to libpq the interface is very familiar. #3 relies on #2 and not sure what it adds as the docs did not install correctly, nor are they accessible from CRAN. All 3 packages compiled and installed on Mac OS X even though #2 and #3 reported failures in CRAN. Have not tried to actually use them yet. Conclusion ========= Use #2 even though it does not seem very mature. Questions ======== Is this a good summary of the state of accessing postgresql from R or have I missed something? Is RPostgreSQL being used anyplace and is it stable?
Yes, I do use it on a regular basis. Though there are some quirks here and there with the functions, they are usable. Often I have found myself writing small wrapper functions around the ones provided with RPostgreSQL for my special needs. I have never tried the other options so no comments on them. But I remember, some time back, reading the documentations and then finally settling on RPostgreSQL for its clarity in implementation and function usage. I use it completely under Linux to Linux connections, so any Mac OSX specific issues will be hidden from me. Finally, I should also mention that my usage is primarily like this: extract relevant/subset of records (and fields) from the database and work inside R with that. I have never tried any complicated/fancy database coding, though, in principle it should work, Dirk might confirm. -- Prasenjit
If you care to be more specific about the "Quirks" and why you did your wrappers I would be interested. I am working with Dirk and others to get the test environments set up on CRAN and r-forge for Mac. Maybe some tests need to be added. Also I have a lot of experience with C drivers and probably can propose some improvements. Neil