dbAppendTable()
is overridden because RPostgres
uses placeholders of the form $1
, $2
etc. instead of ?
.
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 PqConnection
dbAppendTable(conn, name, value, copy = NULL, ..., row.names = NULL)# S4 method for PqConnection,Id
dbExistsTable(conn, name, ...)
# S4 method for PqConnection,character
dbExistsTable(conn, name, ...)
# S4 method for PqConnection,Id
dbListFields(conn, name, ...)
# S4 method for PqConnection,character
dbListFields(conn, name, ...)
# S4 method for PqConnection
dbListObjects(conn, prefix = NULL, ...)
# S4 method for PqConnection
dbListTables(conn, ...)
# S4 method for PqConnection,character
dbReadTable(conn, name, ..., check.names = TRUE, row.names = FALSE)
# S4 method for PqConnection,character
dbRemoveTable(conn, name, ..., temporary = FALSE, fail_if_missing = TRUE)
# S4 method for 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 PqConnection
sqlData(con, value, row.names = FALSE, ...)
a '>PqConnection object, produced by
DBI::dbConnect()
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 dbQuoteIdentifier()
,
an Id()
object, or a string escaped with SQL()
.
A data.frame to write to the database.
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.
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
.
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.
If TRUE
, the default, column names will be
converted to valid R identifiers.
If TRUE
, only temporary tables are considered.
If FALSE
, dbRemoveTable()
succeeds if the
table doesn't exist.
a logical specifying whether to overwrite an existing table
or not. Its default is FALSE
.
a logical specifying whether to append to an existing table
in the DBMS. Its default is FALSE
.
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
.
A database connection.
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.
# NOT RUN {
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)
# }
Run the code above in your browser using DataLab