INSERT {pgTools}

Generate a PostgreSQL INSERT statement, optionally execute the statement if con is not NULL.

Description

Generate a PostgreSQL INSERT statement, optionally execute the statement if con is not NULL.

Usage

INSERT(
  x = NULL,
  schema = NULL,
  table,
  types = NULL,
  returning = NULL,
  quote_text = TRUE,
  cast = TRUE,
  prepare = TRUE,
  batch_size = 50000,
  double_quote_names = FALSE,
  select = NULL,
  select_cols = NULL,
  con = NULL,
  n_cores = 1,
  table_is_temporary = FALSE,
  retain_insert_order = FALSE,
  connect_db_name = NULL
)

Arguments

x

A list, data.frame or data.table, names must match the column names of the destination SQL table.

schema

A string, the schema name of the destination SQL table.

table

A string, the table name of the destination SQL table.

types

A vector of character strings specifying the SQL data types of the destination columns, the position of the type should match the position of the column for that type in x. Required if prepare or cast is TRUE.

returning

A vector of character strings specifying the SQL column names to be returned by the INSERT statement.

quote_text

TRUE/FALSE, if TRUE, calls quoteText() to add single quotes around character strings.

cast

TRUE/FALSE, if TRUE, will add SQL to cast the data to be inserted to the specified type.

prepare

TRUE/FALSE, if TRUE, creates a PostgreSQL prepared statement for inserting the data.

batch_size

Integer, the maximum number of records to submit in one statement.

double_quote_names

TRUE/FALSE, if TRUE, adds double quotes to column names.

select

A string, a SELECT statement.

select_cols

A character vector of the columns to insert the results of the select statement. Only used if select is not NULL.

con

A database connection that can be passed to DBI::dbSendQuery/DBI::dbGetQuery.

n_cores

A integer, the number of cores to use for parallel forking (passed to parallel::mclapply as mc.cores).

table_is_temporary

TRUE/FALSE, if TRUE, prevents parallel processing.

retain_insert_order

TRUE/FALSE, if TRUE, prevents parallel processing.

connect_db_name

The name of the database to pass to connect() when inserting in parallel.

Value

A string, PostgreSQL INSERT statement; or a string, PostgreSQL prepared statement; or the results retrieved by DBI::dbGetQuery after executing the statement.

Examples

INSERT(
x = list(col1 = c("a", "b", "c"), col2 = c(1, 2, 3)),
schema = "test",
table = "table1",
prepare = TRUE,
types = c("TEXT", "INTEGER"),
returning = NULL,
quote_text = TRUE,
cast = TRUE
)