RPostgres v1.3.1


Monthly downloads



'Rcpp' Interface to 'PostgreSQL'

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



rcc Codecov test coverage CRAN status

RPostgres is an DBI-compliant interface to the postgres database. It's a ground-up rewrite using C++ and Rcpp. Compared to RPostgreSQL, 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")

Discussions associated with DBI and related database packages take place on R-SIG-DB. The website Databases using R describes the tools and best practices in this ecosystem.

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.

Please note that the 'RPostgres' project is released with a Contributor Code of Conduct. By contributing to this project, you agree to abide by its terms.

Functions in RPostgres

Name Description
quote Quote postgres strings, identifiers, and literals
Redshift Redshift driver/connection
postgresWaitForNotify Wait for and return any notifications that return within timeout
postgresHasDefault Check if default database is available.
reexports Objects exported from other packages
PqDriver-class PqDriver and methods.
dbIsValid,PqDriver-method PostgreSQL results.
Postgres Postgres driver
PqConnection-class PqConnection and methods.
dbDataType,PqConnection-method Determine database type for R vector.
postgres-query Execute a SQL statement on a database connection
RPostgres-package RPostgres: 'Rcpp' Interface to 'PostgreSQL'
postgres-tables Convenience functions for reading/writing DBMS tables
postgres-transactions Transaction management.
No Results!

Vignettes of RPostgres

No Results!

Last month downloads


Date 2021-01-19
License GPL-3
URL https://rpostgres.r-dbi.org, https://github.com/r-dbi/RPostgres
BugReports https://github.com/r-dbi/RPostgres/issues
LinkingTo BH, plogr (>= 0.2.0), Rcpp
VignetteBuilder knitr
Encoding UTF-8
LazyLoad true
RoxygenNote 7.1.1
SystemRequirements libpq >= 9.0: libpq-dev (deb) or postgresql-devel (rpm)
Collate 'PqDriver.R' 'PqConnection.R' 'PqResult.R' 'RPostgres-pkg.R' 'RcppExports.R' 'Redshift.R' 'default.R' 'export.R' 'names.R' 'quote.R' 'tables.R' 'transactions.R' 'utils.R'
NeedsCompilation yes
Packaged 2021-01-19 13:06:02 UTC; kirill
Repository CRAN
Date/Publication 2021-01-19 13:50:05 UTC

Include our badge in your README