postgres-tables.Rd

% Generated by roxygen2: do not edit by hand
% Please edit documentation in R/tables.R
\name{postgres-tables}
\alias{postgres-tables}
\alias{dbWriteTable,PqConnection,character,data.frame-method}
\alias{dbWriteTable,PqConnection,character,character-method}
\alias{sqlData,PqConnection-method}
\alias{dbAppendTable,PqConnection-method}
\alias{dbReadTable,PqConnection,character-method}
\alias{dbListTables,PqConnection-method}
\alias{dbExistsTable,PqConnection,character-method}
\alias{dbExistsTable,PqConnection,Id-method}
\alias{dbRemoveTable,PqConnection,character-method}
\alias{dbListFields,PqConnection,character-method}
\alias{dbListFields,PqConnection,Id-method}
\alias{dbListObjects,PqConnection-method}
\title{Convenience functions for reading/writing DBMS tables}
\usage{
\S4method{dbWriteTable}{PqConnection,character,data.frame}(
  conn,
  name,
  value,
  ...,
  row.names = FALSE,
  overwrite = FALSE,
  append = FALSE,
  field.types = NULL,
  temporary = FALSE,
  copy = NULL
)

\S4method{dbWriteTable}{PqConnection,character,character}(
  conn,
  name,
  value,
  ...,
  field.types = NULL,
  overwrite = FALSE,
  append = FALSE,
  header = TRUE,
  colClasses = NA,
  row.names = FALSE,
  nrows = 50,
  sep = ",",
  na.strings = "NA",
  eol = "\\n",
  skip = 0,
  temporary = FALSE
)

\S4method{sqlData}{PqConnection}(con, value, row.names = FALSE, ...)

\S4method{dbAppendTable}{PqConnection}(conn, name, value, copy = NULL, ..., row.names = NULL)

\S4method{dbReadTable}{PqConnection,character}(conn, name, ..., check.names = TRUE, row.names = FALSE)

\S4method{dbListTables}{PqConnection}(conn, ...)

\S4method{dbExistsTable}{PqConnection,character}(conn, name, ...)

\S4method{dbExistsTable}{PqConnection,Id}(conn, name, ...)

\S4method{dbRemoveTable}{PqConnection,character}(conn, name, ..., temporary = FALSE, fail_if_missing = TRUE)

\S4method{dbListFields}{PqConnection,character}(conn, name, ...)

\S4method{dbListFields}{PqConnection,Id}(conn, name, ...)

\S4method{dbListObjects}{PqConnection}(conn, prefix = NULL, ...)
}
\arguments{
\item{conn}{a \linkS4class{PqConnection} object, produced by
\code{\link[DBI:dbConnect]{DBI::dbConnect()}}}

\item{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 \code{\link[=dbQuoteIdentifier]{dbQuoteIdentifier()}},
an \code{\link[=Id]{Id()}} object, or a string escaped with \code{\link[=SQL]{SQL()}}.}

\item{value}{A data.frame to write to the database.}

\item{...}{Ignored.}

\item{row.names}{Either \code{TRUE}, \code{FALSE}, \code{NA} or a string.

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

A string is equivalent to \code{TRUE}, but allows you to override the
default name.

For backward compatibility, \code{NULL} is equivalent to \code{FALSE}.}

\item{overwrite}{a logical specifying whether to overwrite an existing table
or not. Its default is \code{FALSE}.}

\item{append}{a logical specifying whether to append to an existing table
in the DBMS. Its default is \code{FALSE}.}

\item{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 \code{\link[DBI:dbDataType]{DBI::dbDataType()}}).
The types can only be specified with \code{append = FALSE}.}

\item{temporary}{If \code{TRUE}, only temporary tables are considered.}

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

\item{header}{is a logical indicating whether the first data line (but see
\code{skip}) has a header or not.  If missing, it value is determined
following \code{\link[=read.table]{read.table()}} convention, namely, it is set to TRUE if
and only if the first row has one fewer field that the number of columns.}

\item{colClasses}{Character vector of R type names, used to override
defaults when imputing classes from on-disk file.}

\item{nrows}{Number of rows to read to determine types.}

\item{sep}{The field separator, defaults to \code{','}.}

\item{na.strings}{a character vector of strings which are to be interpreted as NA values.}

\item{eol}{The end-of-line delimiter, defaults to \code{'\\n'}.}

\item{skip}{number of lines to skip before reading the data. Defaults to 0.}

\item{con}{A database connection.}

\item{check.names}{If \code{TRUE}, the default, column names will be
converted to valid R identifiers.}

\item{fail_if_missing}{If \code{FALSE}, \code{dbRemoveTable()} succeeds if the
table doesn't exist.}

\item{prefix}{A fully qualified path in the database's namespace, or \code{NULL}.
This argument will be processed with \code{\link[DBI:dbUnquoteIdentifier]{dbUnquoteIdentifier()}}.
If given the method will return all objects accessible through this prefix.}
}
\description{
\code{\link[=dbWriteTable]{dbWriteTable()}} executes several SQL statements that
create/overwrite a table and fill it with values.
\pkg{RPostgres} does not use parameterised queries to insert rows because
benchmarks revealed that this was considerably slower than using a single
SQL string.

\code{\link[=dbAppendTable]{dbAppendTable()}} is overridden because \pkg{RPostgres}
uses placeholders of the form \verb{$1}, \verb{$2} etc. instead of \verb{?}.
}
\section{Schemas, catalogs, tablespaces}{

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

\examples{
\dontshow{if (postgresHasDefault()) (if (getRversion() >= "3.4") withAutoprint else force)(\{ # examplesIf}
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)
\dontshow{\}) # examplesIf}
}