'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.


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

Basic usage

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

Connecting to a specific Postgres instance

# 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.


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.


1.1.1 by Kirill Müller, 17 days ago


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 sf.

See at CRAN