[R-SIG-Finance] DBI solution

[Gabor Grothendieck]

On Fri, Nov 4, 2011 at 7:08 PM, Ben Nachtrieb <ben.nachtrieb at gmail.com> wrote:
> Hello,
>
> I'm building (from the ground up) a PostgreSQL time series database for use
> with R. I'd like to get some preliminary guidance (help me get pointed in
> the correct direction).
>
> Here are some details: I am PC/Windows centric. I have data of all
> frequencies equal to or greater than Daily (no inter-day data). We have
> prices and technical data, macro data, company descriptive, and financial
> data. I don't have to do any 'DBA' stuff per-say (no messy stuff like split
> adjustments, ticker changes, etc.) as that is done for me; however, I will
> have to adjust for look-ahead, create new factor values, transform, etc.
> the data that we have and store it in database form. Can someone point me
> to the best solution/packages for this given that I have to stay in the R
> and PostgreSQL world?
>
> I see RpgSQL, RposgreSQL, TSPostgreSQL, DBI, TSdbi, and much more... I am
> hoping someone can narrow things down for me.
>
> Thanks so much!

RpgSQL and RPostgreSQL are DBI-based drivers for R and the others are
higher level packages.

I can only address my own package but RpgSQL was developed for use
with sqldf.  It uses RJDBC (which uses JDBC and Java) and DBI.
Feedback is that RpgSQL is easier to install on Windows than
alternatives.  The use of RJDBC/JDBC may slow it down relative to
alternatives although speed may not be material if you are just
importing a bunch of time series and then working with them in R as
opposed to constantly going back to the database.

sqldf is a package that lets you use R data frames with several
database back-ends including RpgSQL.  You would not use sqldf with
your own database but might use it with R data frames to play around
with PostgreSQL.

Resources are

- the Installation info in this link:
  http://cran.r-project.org/web/packages/RpgSQL/index.html

- ?pgSQL help page (see example at end of page)

- http://sqldf.googlecode.com
  most of which is about sqldf and sqlite although much of it applies
and there is a bit about using  PostgreSQL with sqldf.

-- 
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com