'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.1.1 by Kirill Müller, 6 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] , 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