RPostgres

'Rcpp' Interface to 'PostgreSQL'

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

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

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.