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 RPostgresSQL, 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.1.1 (2018-05-05)

  • Add support for bigint argument to dbConnect(), supported values are "integer64", "integer", "numeric" and "character". Large integers are returned as values of that type (r-dbi/DBItest#133).
  • Data frames resulting from a query always have unique non-empty column names (r-dbi/DBItest#137).
  • New arguments temporary and fail_if_missing (default: TRUE) to dbRemoveTable() (r-dbi/DBI#141, r-dbi/DBI#197).
  • Using dbCreateTable() and dbAppendTable() internally (r-dbi/DBI#74).
  • The field.types argument to dbWriteTable() now must be named.
  • Using current_schemas(true) also in dbListObjects() and dbListTables(), for consistency with dbListFields(). Objects from the pg_catalog schema are still excluded.
  • dbListFields() doesn't list fields from tables found in the pg_catalog schema.
  • The dbListFields() method now works correctly if the name argument is a quoted identifier or of class Id, and throws an error if the table is not found (r-dbi/DBI#75).
  • Implement format() method for SqliteConnection (r-dbi/DBI#163).
  • Reexporting Id(), DBI::dbIsReadOnly() and DBI::dbCanConnect().
  • Now imports DBI 1.0.0.

RPostgres 1.1.0 (2018-04-04)

  • Breaking change: dbGetException() is no longer reexported from DBI.
  • Make "typname" information available after dbFetch() and dbGetQuery(). Values of unknown type are returned as character vector of class "pq_xxx", where "xxx" is the "typname" returned from PostgreSQL. In particular, JSON and JSONB values now have class "pq_json" and "pq_jsonb", respectively. The return value of dbColumnInfo() gains new columns ".oid" (integer), ". known" (logical) and ".typname" (character) (#114, @etiennebr).
  • Values of class "integer64" are now supported for dbWriteTable() and dbBind() (#178).
  • Schema support, as specified by DBI: dbListObjects(), dbUnquoteIdentifier() and Id().
  • Names in the x argument to dbQuoteIdentifier() are preserved in the output (r-lib/DBI#173).
  • All generics defined in DBI (e.g., dbGetQuery()) are now exported, even if the package doesn't provide a custom implementation (#168).
  • Replace non-portable timegm() with private implementation.
  • Correct reference to RPostgreSQL package (#165, @ClaytonJY).

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.4.3 by Kirill Müller, a month ago


https://rpostgres.r-dbi.org, 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] , RStudio [cph] , R Consortium [fnd] , Tomoaki Nishiyama [ctb] (Code for encoding vectors into strings derived from RPostgreSQL)


Documentation:   PDF Manual  


Task views: Databases with R


GPL-3 license


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

Suggests callr, covr, DBItest, knitr, rmarkdown, testthat

Linking to plogr, Rcpp

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


Imported by DIZutils, DQAstats, RGreenplum, ReviewR, crestr, ggquickeda, mfdb, nodbi, stacomirtools.

Suggested by dbplyr, dbx, dittodb, dm, geojsonio, lgrExtra, pointblank, repana, resourcer, rocker, rpostgis, sf, storr.


See at CRAN