[R-sig-DB] RPostgreSQL Row Inserts on Remote Servers

[Gabor Grothendieck]

On Tue, Jul 20, 2010 at 11:37 AM, McGehee, Robert
<Robert.McGehee using geodecapital.com> wrote:
> Hello,
> An issue discussed before on this list server is difficulty using
> dbWriteTable from the RPostgreSQL/DBI package to insert rows when
> either:
> 1) the R client and PostgreSQL server are on different computers (or at
> least don't share a common filesystem), and thus bulk copy is
> unavailable.
> 2) the postgres user does not have read permission for the user's file
> (See https://stat.ethz.ch/pipermail/r-sig-db/2009q4/000741.html, for
> example)
>
> To address this, I wrote a helper function called dbInsert() that some
> of you may find useful. The function converts a data frame into a single
> INSERT query transaction that inserts the data frame into an existing
> table. My hope is that this is a relatively fast way to insert rows when
> bulk copy is unavailable.
>
> If any of you have already discovered a faster way to insert data
> without using bulk copy, or find ways of improving this code, please
> share. I'm also CC:ing the RPostgreSQL package maintainer in case he
> thinks this functionality should be merged into the postgresWriteTable
> function for when bulk copy is not possible.

I haven't tested the speed relative to other drivers but I have a
PostgreSQL driver on CRAN,
RpgSQL which supports PostgreSQL via a layer over RJDBC.

It was mainly developed for use with the sqldf package (which now
supports sqlite, H2 and
PostgreSQL databases) since it needed functionality not supported by
other CRAN drivers.