Write/read in data frame mode to/from database table.

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 existing data.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., with dbDrop()).
only.defs: Logical; if TRUE, only the table definitions will be written.

Value

invisible TRUE for successful write with dbWriteDataFrame, data.frame for dbReadDataFrame

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.frames to the database (including all writing into existing database tables), use pgWriteGeom() with df.mode = FALSE.

Author

David Bucklin david.bucklin@gmail.com

Adrián Cidre González adrian.cidre@gmail.com

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.
} # }