dbWriteDataFrame {rpostgis}

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

Description

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.

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.

Value

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

Author(s)

David Bucklin [e-mail]

Adrián Cidre González [e-mail]

Examples

## Not run: 
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.

## End(Not run)