postgis_insert: Insert or update records in a PostgreSQL table from a R data frame.

Description

These functions produce INSERT (postgis_insert) or UPDATE (postgis_update) queries to write data from a R data frame to a PostgreSQL table, with options to include a geometry layer and a list-column of key-value pairs (as a PostgreSQL hstore). The queries are passed to the database with dbSendQuery.

Usage

postgis_insert(conn, df, tbl, write_cols = NA,
  geom_name = NA_character_, hstore_name = NA_character_)
postgis_update(conn, df, tbl, id_cols, update_cols,
  geom_name = NA_character_, hstore_name = NA_character_,
  hstore_concat = TRUE)

Arguments

conn

A PostgreSQLConnection-class object, such as the output of dbConnect.

A data frame (if geom_name = NA) or Spatial[Points/Lines/Polygons]DataFrame.

tbl

Name of the PostgreSQL table to write to.

write_cols

A character vector, corresponding to the columns in df to insert in the database table. If NA, inserts all columns.

geom_name

Name of the geometry column in the database table (NA if none).

hstore_name

Name of the hstore column in both df and the database table (NA if none).

id_cols

A character vector, corresponding to the columns in df used to match records between df and the database table.

update_cols

A character vector, corresponding to the columns that must be updated in the database table based on values in df.

hstore_concat

If TRUE, hstore columns are updated by concatenation.

Value

The result of dbSendQuery.

Details

All column names used in the query must match between the input data frame and the target database table (except for geom_name which only applies to the table).

postgis_update creates an UPDATE ... SET ... FROM ... query, which effectively joins the original table and input data frame based on matching values in id_cols, then updates the values in update_cols. The combination of id_cols must be unique in df, but they can be duplicated in the database table, in which case multiple rows are updated from a single row in df. Neither the geometry nor the hstore column can be used in id_cols.

Note that if hstore_concat = TRUE (the default), hstore columns are updated by concatenation, i.e. new keys are added, values associated with existing keys are updated, no keys are deleted. To overwrite whole hstore "cells", potentially deleting keys absent in df, set hstore_concat = FALSE.

Examples

Run this code

# NOT RUN {
library(RPostgreSQL)
con <- dbConnect(PostgreSQL(), dbname = "my_db")

# Returns a SpatialPointsDataFrame
cities <- get_postgis_query(con, "SELECT name, geom, datalist FROM city",
                            geom_name = "geom", hstore_name = "datalist")

# Create a new field in hstore and update DB
cities@data$datalist %->% "pop_density" <-
   cities@data$datalist %->% "population" / cities@data$datalist %->% "area"
postgis_update(con, cities, "city",
               id_cols = "name", update_cols = "datalist",
               geom_name = "geom", hstore_name = "datalist")

# Add rows to DB with postgis_insert
# (new_cities is a SpatialPointsDataFrame with same columns as cities)
postgis_insert(con, new_cities, "city",
               geom_name = "geom", hstore_name = "datalist")
# }
# NOT RUN {
# }