[R-sig-DB] Postgresql Options for Mac OS X

[Neil Tiffin]

On May 14, 2009, at 12:13 AM, Prasenjit Kapat wrote:

> Hi,
>
> On Mon, May 11, 2009 at 6:18 PM, Neil Tiffin <neilt using 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