pgInsert: Inserts data into a PostgreSQL table.

In mablab/rpostgis: R Interface to a 'PostGIS' Database

Inserts data into a PostgreSQL table.

Description

'r lifecycle::badge("deprecated")'

This function has been deprecated in favour of [pgWriteGeom()] and will be removed in a future release.

This function takes a take an R sp object (Spatial* or Spatial*DataFrame), or a regular data.frame, and performs the database insert (and table creation, when the table does not exist) on the database.

If new.id is specified, a new sequential integer field is added to the data frame for insert. For Spatial*-only objects (no data frame), a new ID column is created by default with name "gid".

This function will use st_as_text for geography types, and st_as_binary for geometry types.

In the event of function or database error, the database uses ROLLBACK to revert to the previous state.

If the user specifies return.pgi = TRUE, and data preparation is successful, the function will return a pgi object (see next paragraph), regardless of whether the insert was successful or not. This object can be useful for debugging, or re-used as the data.obj in pgInsert; (e.g., when data preparation is slow, and the exact same data needs to be inserted into tables in two separate tables or databases). If return.pgi = FALSE (default), the function will return TRUE for successful insert and FALSE for failed inserts.

Use this function with df.mode = TRUE to save data frames from Spatial*-class objects to the database in "data frame mode". Along with normal dbwriteDataFrame operation, the proj4string of the spatial data will also be saved, and re-attached to the data when using pgGetGeom to import the data. Note that other attributes of Spatial* objects are not saved (e.g., coords.nrs, which is used to specify the column index of x/y columns in SpatialPoints*).

pgi objects are a list containing four character strings: (1) in.table, the table name which will be created or inserted into (2) db.new.table, the SQL statement to create the new table, (3) db.cols.insert, a character string of the database column names to insert into, and (4) insert.data, a character string of the data to insert.

Usage

pgInsert(
  conn,
  name,
  data.obj,
  geom = "geom",
  df.mode = FALSE,
  partial.match = FALSE,
  overwrite = FALSE,
  new.id = NULL,
  row.names = FALSE,
  upsert.using = NULL,
  alter.names = FALSE,
  encoding = NULL,
  return.pgi = FALSE,
  df.geom = NULL,
  geog = FALSE
)

Arguments

conn	A connection object to a PostgreSQL database
name	A character string specifying a PostgreSQL schema and table name (e.g., name = c("schema","table")). If not already existing, the table will be created. If the table already exists, the function will check if all R data frame columns match database columns, and if so, do the insert. If not, the insert will be aborted. The argument partial.match allows for inserts with only partial matches of data frame and database column names, and overwrite allows for overwriting the existing database table.
data.obj	A Spatial* or Spatial*DataFrame, or data.frame
geom	character string. For Spatial* datasets, the name of geometry/(geography) column in the database table. (existing or to be created; defaults to "geom"). The special name "geog" will automatically set geog to TRUE.
df.mode	Logical; Whether to write the (Spatial) data frame in data frame mode (preserving data frame column attributes and row.names). A new table must be created with this mode (or overwrite set to TRUE), and the row.names, alter.names, and new.id arguments will be ignored (see dbWriteDataFrame for more information).
partial.match	Logical; allow insert on partial column matches between data frame and database table. If TRUE, columns in R data frame will be compared with the existing database table name. Columns in the data frame that exactly match the database table will be inserted into the database table.
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).
new.id	Character, name of a new sequential integer ID column to be added to the table for insert (for spatial objects without data frames, this column is created even if left NULL and defaults to the name "gid"). If partial.match = TRUE and the column does not exist in the database table, it will be discarded.
row.names	Whether to add the data frame row names to the database table. Column name will be '.R_rownames'.
upsert.using	Character, name of the column(s) in the database table or constraint name used to identify already-existing rows in the table, which will be updated rather than inserted. The column(s) must have a unique constraint already created in the database table (e.g., a primary key). Requires PostgreSQL 9.5+.
alter.names	Logical, whether to make database column names DB-compliant (remove special characters/capitalization). Default is FALSE. (This must be set to FALSE to match with non-standard names in an existing database table.)
encoding	Character vector of length 2, containing the from/to encodings for the data (as in the function iconv). For example, if the dataset contain certain latin characters (e.g., accent marks), and the database is in UTF-8, use encoding = c("latin1", "UTF-8"). Left NULL, no conversion will be done.
return.pgi	Whether to return a formatted list of insert parameters (i.e., a pgi object; see function details.)
df.geom	Character vector, name of a character column in an R data.frame storing PostGIS geometries, this argument can be used to insert a geometry stored as character type in a data.frame (do not use with Spatial* data types). If only the column name is used (e.g., df.geom = "geom"), the column type will be a generic (GEOMETRY); use a two-length character vector (e.g., df.geom = c("geom", "(POINT,4326)") to also specify a specific PostGIS geometry type and SRID for the column. Only recommended for for new tables/overwrites, since this method will change the existing column type.
geog	Logical; Whether to write the spatial data as a PostGIS 'GEOGRAPHY' type. By default, FALSE, unless geom = "geog".

Value

Returns TRUE if the insertion was successful, FALSE if failed, or a pgi object if specified.

Author(s)

David Bucklin [e-mail]

Examples

## Not run: 
library(sp)
data(meuse)
coords <- SpatialPoints(meuse[, c("x", "y")])
spdf <- SpatialPointsDataFrame(coords, meuse)

## Insert data in new database table
pgInsert(conn, name = c("public", "meuse_data"), data.obj = spdf)

## The same command will insert into already created table (if all R
## columns match)
pgInsert(conn, name = c("public", "meuse_data"), data.obj = spdf)

## If not all database columns match, need to use partial.match = TRUE,
## where non-matching columns are not inserted
colnames(spdf@data)[4] <- "cu"
pgInsert(conn, name = c("public", "meuse_data"), data.obj = spdf,
    partial.match = TRUE)

## End(Not run)

mablab/rpostgis documentation built on Jan. 15, 2024, 10:14 p.m.