R/dbWriteDataFrame.R
dbWriteDataFrame.Rd
Write data.frame
or similar (e.g. tibble
) to database table,
with column definitions, row names, and a new integer primary key column.
Read back into R with dbReadDataFrame
, which recreates original
data.
dbWriteDataFrame(conn, name, df, overwrite = FALSE, only.defs = FALSE)
dbReadDataFrame(conn, name, df = NULL)
A connection object to a PostgreSQL database
Character, schema and table of the PostgreSQL table
The data frame to write (for dbReadDataFrame
, this
allows to update an existing data.frame
with definitions
stored in the database)
Logical; if TRUE, a new table (name
) will
overwrite the existing table (name
) in the database. Note:
overwriting a view must be done manually (e.g., with dbDrop
).
Logical; if TRUE
, only the table
definitions will be written.
TRUE
for successful write with
dbWriteDataFrame
, data.frame
for
dbReadDataFrame
Writing in data frame mode is only for new database tables (or for overwriting an existing one). It will save all column names as they appear in R, along with column data types and attributes. This is done by adding metadata to a lookup table in the table's schema named ".R_df_defs" (will be created if not present). It also adds two fields with fixed names to the database table: ".R_rownames" (storing the row.names of the data frame), and ".db_pkid", which is a new integer primary key. Existing columns in the data.frame matching these names will be automatically changed.
The rpostgis
database table read functions
dbReadDataFrame
and pgGetGeom
will use the metadata
created in data frame mode to recreate a data.frame in R, if it is
available. Otherwise, it will be imported using default
RPostgreSQL::dbGetQuery
methods.
All spatial objects must be written with pgWriteGeom
.
For more flexible writing of data.frame
s to the database
(including all writing into existing database tables), use
pgWriteGeom
with df.mode = FALSE
.
if (FALSE) {
library(datasets)
## Write the mtcars data.frame to the database:
dbWriteDataFrame(conn, name = "mtcars_data", df = mtcars)
## Reads it back into a different object:
mtcars2 <- dbReadDataFrame(conn, name = "mtcars_data")
## Check equality:
all.equal(mtcars, mtcars2)
## Should return TRUE.
}