rpostgis (version 1.4.3)

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

Description

Write data.frame 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 frame.

Usage

dbWriteDataFrame(conn, name, df, overwrite = FALSE, only.defs = FALSE)
dbReadDataFrame(conn, name, df = NULL)

Value

TRUE for successful write with

dbWriteDataFrame, data.frame for

dbReadDataFrame

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.

Author

David Bucklin

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*DataFrames must be written with pgInsert. For more flexible writing of data.frames to the database (including all writing into existing database tables), use pgInsert with df.mode = FALSE.

Examples

if (FALSE) {
library(sp)
data(meuse)

## Write the data.frame to the database:
dbWriteDataFrame(conn, name = "meuse_data", df = meuse)

## Reads it back into a different object:
me2 <- dbReadDataFrame(conn, name = "meuse_data")

## Check equality:
all.equal(meuse, me2)
## Should return TRUE.
}