'Rcpp' Interface to 'PostgreSQL'

Fully 'DBI'-compliant 'Rcpp'-backed interface to 'PostgreSQL' < https://www.postgresql.org/>, an open-source relational database.


Travis-CI Build Status AppVeyor Build Status codecov

RPostgres is an DBI-compliant interface to the postgres database. It's a ground-up rewrite using C++ and Rcpp. Compared to PostgresSQL, it:

  • Has full support for parameterised queries via dbSendQuery(), and dbBind().

  • Automatically cleans up open connections and result sets, ensuring that you don't need to worry about leaking connections or memory.

  • Is a little faster, saving ~5 ms per query. (For reference, it takes around 5ms to retrive a 1000 x 25 result set from a local database, so this is decent speed up for smaller queries.)

  • A simplified build process that relies on system libpq.

Installation

# Install the latest RPostgres release from CRAN:
install.packages("RPostgres")
 
# Or the the development version from GitHub:
# install.packages("remotes")
remotes::install_github("r-dbi/RPostgres")

Basic usage

library(DBI)
# Connect to the default postgres database
con <- dbConnect(RPostgres::Postgres())
 
dbListTables(con)
dbWriteTable(con, "mtcars", mtcars)
dbListTables(con)
 
dbListFields(con, "mtcars")
dbReadTable(con, "mtcars")
 
# You can fetch all results:
res <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = 4")
dbFetch(res)
dbClearResult(res)
 
# Or a chunk at a time
res <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = 4")
while(!dbHasCompleted(res)){
  chunk <- dbFetch(res, n = 5)
  print(nrow(chunk))
}
# Clear the result
dbClearResult(res)
 
# Disconnect from the database
dbDisconnect(con)

Connecting to a specific Postgres instance

library(DBI)
# Connect to a specific postgres database i.e. Heroku
con <- dbConnect(RPostgres::Postgres(),dbname = 'DATABASE_NAME', 
                 host = 'HOST', # i.e. 'ec2-54-83-201-96.compute-1.amazonaws.com'
                 port = 5432, # or any other port specified by your DBA
                 user = 'USERNAME',
                 password = 'PASSWORD')
 

Design notes

The original DBI design imagined that each package could instantiate X drivers, with each driver having Y connections and each connection having Z results. This turns out to be too general: a driver has no real state, for PostgreSQL each connection can only have one result set. In the RPostgres package there's only one class on the C side: a connection, which optionally contains a result set. On the R side, the driver class is just a dummy class with no contents (used only for dispatch), and both the connection and result objects point to the same external pointer.

News

RPostgres 1.0-4 (2017-12-20)

  • Only call PQcancel() if the query hasn't completed, fixes transactions on Amazon RedShift (#159, @mmuurr).
  • Fix installation on MacOS.
  • Check libpq version in configure script (need at least 9.0).
  • Fix UBSAN warning: reference binding to null pointer (#156).
  • Fix rchk warning: PROTECT internal temporary SEXP objects (#157).
  • Fix severe memory leak when fetching results (#154).

RPostgres 1.0-3 (2017-12-01)

Initial release, compliant to the DBI specification.

  • Test almost all test cases of the DBI specification.
  • Fully support parametrized queries.
  • Spec-compliant transactions.
  • 64-bit integers are now supported through the bit64 package. This also means that numeric literals (as in SELECT 1) are returned as 64-bit integers. The bigint argument to dbConnect() allows overriding the data type on a per-connection basis.
  • Correct handling of DATETIME and TIME columns.
  • New default row.names = FALSE.

Reference manual

It appears you don't have a PDF plugin for this browser. You can click here to download the reference manual.

install.packages("RPostgres")

1.1.1 by Kirill Müller, 2 months ago


https://github.com/r-dbi/RPostgres


Report a bug at https://github.com/r-dbi/RPostgres/issues


Browse source code at https://github.com/cran/RPostgres


Authors: Hadley Wickham [aut], Jeroen Ooms [aut], Kirill Müller [aut, cre] (<https://orcid.org/0000-0002-1416-3412>), RStudio [cph], R Consortium [fnd], Tomoaki Nishiyama [ctb] (Code for encoding vectors into strings derived from RPostgreSQL)


Documentation:   PDF Manual  


GPL-2 license


Imports bit64, blob, DBI, hms, methods, Rcpp, withr

Suggests DBItest, testthat

Linking to BH, plogr, Rcpp

System requirements: libpq >= 9.0: libpq-dev (deb) or postgresql-devel (rpm)


Imported by RGreenplum.

Suggested by dbx, sf, storr.


See at CRAN