postgres-tables function

Convenience functions for reading/writing DBMS tables

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.

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