![](../logo.png)
Write/read in data frame mode to/from database table.
Source: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.
Usage
dbWriteDataFrame(conn, name, df, overwrite = FALSE, only.defs = FALSE)
dbReadDataFrame(conn, name, df = NULL)
Arguments
- conn
A connection object to a PostgreSQL database
- name
Character, schema and table of the PostgreSQL table
- df
The data frame to write (for
dbReadDataFrame
, this allows to update an existingdata.frame
with definitions stored in the database)- overwrite
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., withdbDrop
).- only.defs
Logical; if
TRUE
, only the table definitions will be written.
Details
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
.
Examples
if (FALSE) { # \dontrun{
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.
} # }