Convenience functions for reading/writing DBMS tables

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.

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

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

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

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

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

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

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

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

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

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

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

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

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.
value	A data.frame to write to the database.
...	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`.
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 inferred with `DBI::dbDataType()`).
temporary	If `TRUE`, only temporary tables are considered.
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.
con	A database connection.
check.names	If `TRUE`, the default, column names will be converted to valid R identifiers.
fail_if_missing	If `FALSE`, `dbRemoveTable()` succeeds if the table doesn't exist.
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.

Examples

# For running the examples on systems without PostgreSQL connection:
run <- postgresHasDefault()
#> Could not initialise default postgres database. If postgres is running
#> check that the environment variables PGHOST, PGPORT, 
#> PGUSER, PGPASSWORD, and PGDATABASE, are defined and
#> point to your database.

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

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

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

if (run) dbDisconnect(con)