postgis_insert_update: Insert or update records in a PostgreSQL table from a R data...
In postGIStools: Tools for Interacting with 'PostgreSQL' / 'PostGIS' Databases

Description Usage Arguments Details Value See Also Examples

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.

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)

`conn`	A `PostgreSQLConnection-class` object, such as the output of `dbConnect`.
`df`	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.

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.

The result of dbSendQuery.

get_postgis_query for the inverse operation (read from database to R).

## 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")

## End(Not run)