Help for package RPostgres

Title:

C++ Interface to PostgreSQL

Version:

1.4.8

Date:

2025-02-24

Description:

Fully DBI-compliant C++-backed interface to PostgreSQL https://www.postgresql.org/, an open-source relational database.

License:

MIT + file LICENSE

URL:

https://rpostgres.r-dbi.org, https://github.com/r-dbi/RPostgres

BugReports:

https://github.com/r-dbi/RPostgres/issues

Depends:

R (≥ 3.1.0)

Imports:

bit64, blob (≥ 1.2.0), DBI (≥ 1.2.0), hms (≥ 1.0.0), lubridate, methods, withr

Suggests:

callr, covr, DBItest (≥ 1.7.3), knitr, rlang, rmarkdown, testthat (≥ 3.0.0)

LinkingTo:

cpp11, plogr (≥ 0.2.0)

Config/Needs/website:

r-dbi/dbitemplate

VignetteBuilder:

knitr

Config/Needs/build:

decor

Config/autostyle/scope:

line_breaks

Config/autostyle/strict:

false

Config/testthat/edition:

Encoding:

UTF-8

LazyLoad:

true

RoxygenNote:

7.3.2.9000

SystemRequirements:

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

Collate:

'PqDriver.R' 'PqConnection.R' 'PqResult.R' 'RPostgres-pkg.R' 'Redshift.R' 'cpp11.R' 'dbAppendTable_PqConnection.R' 'dbBegin_PqConnection.R' 'dbBind_PqResult.R' 'dbClearResult_PqResult.R' 'dbColumnInfo_PqResult.R' 'dbCommit_PqConnection.R' 'dbConnect_PqDriver.R' 'dbConnect_RedshiftDriver.R' 'dbDataType_PqConnection.R' 'dbDataType_PqDriver.R' 'dbDisconnect_PqConnection.R' 'dbExistsTable_PqConnection_Id.R' 'dbExistsTable_PqConnection_character.R' 'dbFetch_PqResult.R' 'dbGetInfo_PqConnection.R' 'dbGetInfo_PqDriver.R' 'dbGetRowCount_PqResult.R' 'dbGetRowsAffected_PqResult.R' 'dbGetStatement_PqResult.R' 'dbHasCompleted_PqResult.R' 'dbIsValid_PqConnection.R' 'dbIsValid_PqDriver.R' 'dbIsValid_PqResult.R' 'dbListFields_PqConnection_Id.R' 'dbListFields_PqConnection_character.R' 'dbListObjects_PqConnection_ANY.R' 'dbListTables_PqConnection.R' 'dbQuoteIdentifier_PqConnection_Id.R' 'dbQuoteIdentifier_PqConnection_SQL.R' 'dbQuoteIdentifier_PqConnection_character.R' 'dbQuoteLiteral_PqConnection.R' 'dbQuoteString_PqConnection_SQL.R' 'dbQuoteString_PqConnection_character.R' 'dbReadTable_PqConnection_character.R' 'dbRemoveTable_PqConnection_character.R' 'dbRollback_PqConnection.R' 'dbSendQuery_PqConnection.R' 'dbUnloadDriver_PqDriver.R' 'dbUnquoteIdentifier_PqConnection_SQL.R' 'dbWriteTable_PqConnection_character_data.frame.R' 'default.R' 'export.R' 'quote.R' 'show_PqConnection.R' 'sqlData_PqConnection.R' 'tables.R' 'transactions.R' 'utils.R'

NeedsCompilation:

yes

Packaged:

2025-02-25 05:33:04 UTC; kirill

Author:

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)

Maintainer:

Kirill Müller <kirill@cynkra.com>

Repository:

CRAN

Date/Publication:

2025-02-25 07:30:02 UTC

RPostgres: C++ Interface to PostgreSQL

Description

Fully DBI-compliant C++-backed interface to PostgreSQL https://www.postgresql.org/, an open-source relational database.

Author(s)

Maintainer: Kirill Müller kirill@cynkra.com (ORCID)

Authors:

Hadley Wickham
Jeroen Ooms

Other contributors:

RStudio [copyright holder]
R Consortium [funder]
Tomoaki Nishiyama (Code for encoding vectors into strings derived from RPostgreSQL) [contributor]

Determine database type for R vector.

Description

Determine database type for R vector.

Usage

## S4 method for signature 'PqConnection'
dbDataType(dbObj, obj, ...)

## S4 method for signature 'PqDriver'
dbDataType(dbObj, obj, ...)

Arguments

dbObj

Postgres driver or connection.

obj

Object to convert

Postgres driver

Description

DBI::dbConnect() establishes a connection to a database. Set drv = Postgres() to connect to a PostgreSQL(-ish) database. Use drv = Redshift() instead to connect to an AWS Redshift cluster.

Manually disconnecting a connection is not necessary with RPostgres, but still recommended; if you delete the object containing the connection, it will be automatically disconnected during the next GC with a warning.

Usage

Postgres()

## S4 method for signature 'PqDriver'
dbConnect(
  drv,
  dbname = NULL,
  host = NULL,
  port = NULL,
  password = NULL,
  user = NULL,
  service = NULL,
  ...,
  bigint = c("integer64", "integer", "numeric", "character"),
  check_interrupts = FALSE,
  timezone = "UTC",
  timezone_out = NULL
)

## S4 method for signature 'PqConnection'
dbDisconnect(conn, ...)

Arguments

drv

DBI::DBIDriver. Use Postgres() to connect to a PostgreSQL(-ish) database or Redshift() to connect to an AWS Redshift cluster. Use an existing DBI::DBIConnection object to clone an existing connection.

dbname

Database name. If NULL, defaults to the user name. Note that this argument can only contain the database name, it will not be parsed as a connection string (internally, expand_dbname is set to false in the call to PQconnectdbParams()).

host, port

Host and port. If NULL, will be retrieved from PGHOST and PGPORT env vars.

user, password

User name and password. If NULL, will be retrieved from PGUSER and PGPASSWORD envvars, or from the appropriate line in ⁠~/.pgpass⁠. See https://www.postgresql.org/docs/current/libpq-pgpass.html for more details.

service

Name of service to connect as. If NULL, will be ignored. Otherwise, connection parameters will be loaded from the pg_service.conf file and used. See https://www.postgresql.org/docs/current/libpq-pgservice.html for details on this file and syntax.

...

Other name-value pairs that describe additional connection options as described at https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS

bigint

The R type that 64-bit integer types should be mapped to, default is bit64::integer64, which allows the full range of 64 bit integers.

check_interrupts

Should user interrupts be checked during the query execution (before first row of data is available)? Setting to TRUE allows interruption of queries running too long.

timezone

Sets the timezone for the connection. The default is "UTC". If NULL then no timezone is set, which defaults to the server's time zone.

timezone_out

The time zone returned to R, defaults to timezone. If you want to display datetime values in the local timezone, set to Sys.timezone() or "". This setting does not change the time values returned, only their display.

conn

Connection to disconnect.

Examples


library(DBI)
# Pass more arguments as necessary to dbConnect()
con <- dbConnect(RPostgres::Postgres())
dbDisconnect(con)

Execute a SQL statement on a database connection

Description

To retrieve results a chunk at a time, use dbSendQuery(), dbFetch(), then dbClearResult(). Alternatively, if you want all the results (and they'll fit in memory) use dbGetQuery() which sends, fetches and clears for you.

Usage

## S4 method for signature 'PqResult'
dbBind(res, params, ...)

## S4 method for signature 'PqResult'
dbClearResult(res, ...)

## S4 method for signature 'PqResult'
dbFetch(res, n = -1, ..., row.names = FALSE)

## S4 method for signature 'PqResult'
dbHasCompleted(res, ...)

## S4 method for signature 'PqConnection'
dbSendQuery(conn, statement, params = NULL, ..., immediate = FALSE)

Arguments

res

Code a PqResult produced by DBI::dbSendQuery().

params

A list of query parameters to be substituted into a parameterised query. Query parameters are sent as strings, and the correct type is imputed by PostgreSQL. If this fails, you can manually cast the parameter with e.g. "$1::bigint".

...

Other arguments needed for compatibility with generic (currently ignored).

n

Number of rows to return. If less than zero returns all rows.

row.names

Either TRUE, FALSE, NA or a string.

If TRUE, always translate row names to a column called "row_names". If FALSE, never translate row names. If NA, translate rownames only if they're a character vector.

A string is equivalent to TRUE, but allows you to override the default name.

For backward compatibility, NULL is equivalent to FALSE.

conn

A PqConnection created by DBI::dbConnect().

statement

An SQL string to execute.

immediate

If TRUE, uses the PGsendQuery() API instead of PGprepare(). This allows to pass multiple statements and turns off the ability to pass parameters.

Multiple queries and statements

With immediate = TRUE, it is possible to pass multiple queries or statements, separated by semicolons. For multiple statements, the resulting value of DBI::dbGetRowsAffected() corresponds to the total number of affected rows. If multiple queries are used, all queries must return data with the same column names and types. Queries and statements can be mixed.

Examples


library(DBI)
db <- dbConnect(RPostgres::Postgres())
dbWriteTable(db, "usarrests", datasets::USArrests, temporary = TRUE)

# Run query to get results as dataframe
dbGetQuery(db, "SELECT * FROM usarrests LIMIT 3")

# Send query to pull requests in batches
res <- dbSendQuery(db, "SELECT * FROM usarrests")
dbFetch(res, n = 2)
dbFetch(res, n = 2)
dbHasCompleted(res)
dbClearResult(res)

dbRemoveTable(db, "usarrests")

dbDisconnect(db)

Convenience functions for reading/writing DBMS tables

Description

DBI::dbAppendTable() is overridden because RPostgres uses placeholders of the form ⁠$1⁠, ⁠$2⁠ etc. instead of ⁠?⁠.

DBI::dbWriteTable() executes several SQL statements that create/overwrite a table and fill it with values. RPostgres does not use parameterised queries to insert rows because benchmarks revealed that this was considerably slower than using a single SQL string.

Usage

## S4 method for signature 'PqConnection'
dbAppendTable(conn, name, value, copy = NULL, ..., row.names = NULL)

## S4 method for signature 'PqConnection,Id'
dbExistsTable(conn, name, ...)

## S4 method for signature 'PqConnection,character'
dbExistsTable(conn, name, ...)

## S4 method for signature 'PqConnection,Id'
dbListFields(conn, name, ...)

## S4 method for signature 'PqConnection,character'
dbListFields(conn, name, ...)

## S4 method for signature 'PqConnection'
dbListObjects(conn, prefix = NULL, ...)

## S4 method for signature 'PqConnection'
dbListTables(conn, ...)

## S4 method for signature 'PqConnection,character'
dbReadTable(conn, name, ..., check.names = TRUE, row.names = FALSE)

## S4 method for signature 'PqConnection,character'
dbRemoveTable(conn, name, ..., temporary = FALSE, fail_if_missing = TRUE)

## S4 method for signature 'PqConnection,character,data.frame'
dbWriteTable(
  conn,
  name,
  value,
  ...,
  row.names = FALSE,
  overwrite = FALSE,
  append = FALSE,
  field.types = NULL,
  temporary = FALSE,
  copy = NULL
)

## S4 method for signature 'PqConnection'
sqlData(con, value, row.names = FALSE, ...)

Arguments

conn

a PqConnection object, produced by DBI::dbConnect()

name

a character string specifying a table name. Names will be automatically quoted so you can use any sequence of characters, not just any valid bare table name. Alternatively, pass a name quoted with DBI::dbQuoteIdentifier(), an Id() object, or a string escaped with DBI::SQL().

value

A data.frame to write to the database.

copy

If TRUE, serializes the data frame to a single string and uses ⁠COPY name FROM stdin⁠. This is fast, but not supported by all postgres servers (e.g. Amazon's Redshift). If FALSE, generates a single SQL string. This is slower, but always supported. The default maps to TRUE on connections established via Postgres() and to FALSE on connections established via Redshift().

...

Ignored.

row.names

Either TRUE, FALSE, NA or a string.

If TRUE, always translate row names to a column called "row_names". If FALSE, never translate row names. If NA, translate rownames only if they're a character vector.

A string is equivalent to TRUE, but allows you to override the default name.

For backward compatibility, NULL is equivalent to FALSE.

prefix

A fully qualified path in the database's namespace, or NULL. This argument will be processed with dbUnquoteIdentifier(). If given the method will return all objects accessible through this prefix.

check.names

If TRUE, the default, column names will be converted to valid R identifiers.

temporary

If TRUE, only temporary tables are considered.

fail_if_missing

If FALSE, dbRemoveTable() succeeds if the table doesn't exist.

overwrite

a logical specifying whether to overwrite an existing table or not. Its default is FALSE.

append

a logical specifying whether to append to an existing table in the DBMS. Its default is FALSE.

field.types

character vector of named SQL field types where the names are the names of new table's columns. If missing, types are inferred with DBI::dbDataType()). The types can only be specified with append = FALSE.

con

A database connection.

Schemas, catalogs, tablespaces

Pass an identifier created with Id() as the name argument to specify the schema or catalog, e.g. name = Id(catalog = "my_catalog", schema = "my_schema", table = "my_table") . To specify the tablespace, use dbExecute(conn, "SET default_tablespace TO my_tablespace") before creating the table.

Examples


library(DBI)
con <- dbConnect(RPostgres::Postgres())
dbListTables(con)
dbWriteTable(con, "mtcars", mtcars, temporary = TRUE)
dbReadTable(con, "mtcars")

dbListTables(con)
dbExistsTable(con, "mtcars")

# A zero row data frame just creates a table definition.
dbWriteTable(con, "mtcars2", mtcars[0, ], temporary = TRUE)
dbReadTable(con, "mtcars2")

dbDisconnect(con)

Transaction management.

Description

dbBegin() starts a transaction. dbCommit() and dbRollback() end the transaction by either committing or rolling back the changes.

Usage

## S4 method for signature 'PqConnection'
dbBegin(conn, ..., name = NULL)

## S4 method for signature 'PqConnection'
dbCommit(conn, ..., name = NULL)

## S4 method for signature 'PqConnection'
dbRollback(conn, ..., name = NULL)

Arguments

conn

a PqConnection object, produced by DBI::dbConnect()

...

Unused, for extensibility.

name

If provided, uses the SAVEPOINT SQL syntax to establish, remove (commit) or undo a ßsavepoint.

Value

A boolean, indicating success or failure.

Examples


library(DBI)
con <- dbConnect(RPostgres::Postgres())
dbWriteTable(con, "USarrests", datasets::USArrests, temporary = TRUE)
dbGetQuery(con, 'SELECT count(*) from "USarrests"')

dbBegin(con)
dbExecute(con, 'DELETE from "USarrests" WHERE "Murder" > 1')
dbGetQuery(con, 'SELECT count(*) from "USarrests"')
dbRollback(con)

# Rolling back changes leads to original count
dbGetQuery(con, 'SELECT count(*) from "USarrests"')

dbRemoveTable(con, "USarrests")
dbDisconnect(con)

Check if default database is available.

Description

RPostgres examples and tests connect to a default database via ⁠dbConnect(⁠Postgres()⁠)⁠. This function checks if that database is available, and if not, displays an informative message.

postgresDefault() works similarly but returns a connection on success and throws a testthat skip condition on failure, making it suitable for use in tests.

Usage

postgresHasDefault(...)

postgresDefault(...)

Arguments

...

Additional arguments passed on to DBI::dbConnect()

Examples

if (postgresHasDefault()) {
  db <- postgresDefault()
  print(dbListTables(db))
  dbDisconnect(db)
} else {
  message("No database connection.")
}

Imports a large object from file

Description

Returns an object idenfier (Oid) for the imported large object

Usage

postgresImportLargeObject(conn, filepath = NULL, oid = 0)

Arguments

conn

a PqConnection object, produced by DBI::dbConnect()

filepath

a path to the large object to import

oid

the oid to write to. Defaults to 0 which assigns an unused oid

Value

the identifier of the large object, an integer

Examples

## Not run: 
con <- postgresDefault()
filepath <- 'your_image.png'
dbWithTransaction(con, {
  oid <- postgresImportLargeObject(con, filepath)
})

## End(Not run)

Return whether a transaction is ongoing

Description

Detect whether the transaction is active for the given connection. A transaction might be started with DBI::dbBegin() or wrapped within DBI::dbWithTransaction().

Usage

postgresIsTransacting(conn)

Arguments

conn

a PqConnection object, produced by DBI::dbConnect()

Value

A boolean, indicating if a transaction is ongoing.

Wait for and return any notifications that return within timeout

Description

Once you subscribe to notifications with LISTEN, use this to wait for responses on each channel.

Usage

postgresWaitForNotify(conn, timeout = 1)

Arguments

conn

a PqConnection object, produced by DBI::dbConnect()

timeout

How long to wait, in seconds. Default 1

Value

If a notification was available, a list of:

channel: Name of channel
pid: PID of notifying server process
payload: Content of notification

If no notifications are available, return NULL

Examples


library(DBI)
library(callr)

# listen for messages on the grapevine
db_listen <- dbConnect(RPostgres::Postgres())
dbExecute(db_listen, "LISTEN grapevine")

# Start another process, which sends a message after a delay
rp <- r_bg(function() {
  library(DBI)
  Sys.sleep(0.3)
  db_notify <- dbConnect(RPostgres::Postgres())
  dbExecute(db_notify, "NOTIFY grapevine, 'psst'")
  dbDisconnect(db_notify)
})

# Sleep until we get the message
n <- NULL
while (is.null(n)) {
  n <- RPostgres::postgresWaitForNotify(db_listen, 60)
}
stopifnot(n$payload == 'psst')

# Tidy up
rp$wait()
dbDisconnect(db_listen)

PqConnection and methods.

Description

PqConnection and methods.

Usage

## S3 method for class 'PqConnection'
format(x, ...)

## S4 method for signature 'PqConnection'
dbGetInfo(dbObj, ...)

## S4 method for signature 'PqConnection'
dbIsValid(dbObj, ...)

## S4 method for signature 'PqConnection'
show(object)

PqDriver and methods.

Description

PqDriver and methods.

Usage

## S4 method for signature 'PqDriver'
dbGetInfo(dbObj, ...)

## S4 method for signature 'PqDriver'
dbUnloadDriver(drv, ...)

PostgreSQL results.

Description

PostgreSQL results.

Usage

## S4 method for signature 'PqResult'
dbColumnInfo(res, ...)

## S4 method for signature 'PqResult'
dbGetRowCount(res, ...)

## S4 method for signature 'PqResult'
dbGetRowsAffected(res, ...)

## S4 method for signature 'PqResult'
dbGetStatement(res, ...)

## S4 method for signature 'PqDriver'
dbIsValid(dbObj, ...)

## S4 method for signature 'PqResult'
dbIsValid(dbObj, ...)

Quote postgres strings, identifiers, and literals

Description

If an object of class Id is used for dbQuoteIdentifier(), it needs at most one table component and at most one schema component.

Usage

## S4 method for signature 'PqConnection,Id'
dbQuoteIdentifier(conn, x, ...)

## S4 method for signature 'PqConnection,SQL'
dbQuoteIdentifier(conn, x, ...)

## S4 method for signature 'PqConnection,character'
dbQuoteIdentifier(conn, x, ...)

## S4 method for signature 'PqConnection'
dbQuoteLiteral(conn, x, ...)

## S4 method for signature 'PqConnection,SQL'
dbQuoteString(conn, x, ...)

## S4 method for signature 'PqConnection,character'
dbQuoteString(conn, x, ...)

## S4 method for signature 'PqConnection,SQL'
dbUnquoteIdentifier(conn, x, ...)

Arguments

conn

A PqConnection created by dbConnect()

x

A character vector to be quoted.

...

Other arguments needed for compatibility with generic (currently ignored).

Examples


library(DBI)
con <- dbConnect(RPostgres::Postgres())

x <- c("a", "b c", "d'e", "\\f")
dbQuoteString(con, x)
dbQuoteIdentifier(con, x)
dbDisconnect(con)

Redshift driver/connection

Description

Use drv = Redshift() instead of drv = Postgres() to connect to an AWS Redshift cluster. All methods in RPostgres and downstream packages can be called on such connections. Some have different behavior for Redshift connections, to ensure better interoperability.

Usage

Redshift()

## S4 method for signature 'RedshiftDriver'
dbConnect(
  drv,
  dbname = NULL,
  host = NULL,
  port = NULL,
  password = NULL,
  user = NULL,
  service = NULL,
  ...,
  bigint = c("integer64", "integer", "numeric", "character"),
  check_interrupts = FALSE,
  timezone = "UTC"
)

Arguments

drv

dbname

host, port

Host and port. If NULL, will be retrieved from PGHOST and PGPORT env vars.

user, password

service

...

Other name-value pairs that describe additional connection options as described at https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS

bigint

The R type that 64-bit integer types should be mapped to, default is bit64::integer64, which allows the full range of 64 bit integers.

check_interrupts

Should user interrupts be checked during the query execution (before first row of data is available)? Setting to TRUE allows interruption of queries running too long.

timezone

Sets the timezone for the connection. The default is "UTC". If NULL then no timezone is set, which defaults to the server's time zone.

Objects exported from other packages

Description

These objects are imported from other packages. Follow the links below to see their documentation.

DBI: Id

RPostgres: C++ Interface to PostgreSQL

Description

Author(s)

See Also

Determine database type for R vector.

Description

Usage

Arguments

Postgres driver

Description

Usage

Arguments

Examples

Execute a SQL statement on a database connection

Description

Usage

Arguments

Multiple queries and statements

Examples

Convenience functions for reading/writing DBMS tables

Description

Usage

Arguments

Schemas, catalogs, tablespaces

Examples

Transaction management.

Description

Usage

Arguments

Value

Examples

Check if default database is available.

Description

Usage

Arguments

Examples

Imports a large object from file

Description

Usage

Arguments

Value

Examples

Return whether a transaction is ongoing

Description

Usage

Arguments

Value

Wait for and return any notifications that return within timeout

Description

Usage

Arguments

Value

Examples

PqConnection and methods.

Description

Usage

PqDriver and methods.

Description

Usage

PostgreSQL results.

Description

Usage

Quote postgres strings, identifiers, and literals

Description

Usage

Arguments

Examples

Redshift driver/connection

Description

Usage

Arguments

Objects exported from other packages

Description