table-io: PostgreSQL data frame IO
In rpg: Easy Interface to Advanced PostgreSQL Features

Description Usage Arguments Details Value Note Author(s) See Also Examples

Reads and writes table to and from database

write_table(x, tablename, pkey = NULL, row_names = NULL,
  schemaname = NULL, types = NULL, append = FALSE, overwrite = FALSE)

read_table(tablename, what = "*", limit = NULL, row_names = NULL,
  schemaname = NULL, pkey_to_row_names = FALSE)

`x`	a data frame or something convertible to a data frame
`tablename`	the name of the table to read from or write to
`pkey`	a column name to use as primary key
`row_names`	a column name to write row names
`schemaname`	the schema name
`types`	a list of valid PostgreSQL type names
`append`	if true, append rows to existing table
`overwrite`	if true, destroy existing table with the same name
`what`	a vector of column names
`limit`	only return this many rows
`pkey_to_row_names`	if true and row_names not given, use primary key column

A table is created using the current connection. If pkey does not match any column name, then a new column is created with the name given by pkey. Its type will be serial and it will be set as the primary key. If pkey does match an existing column name, then that column will be used as the primary key. Note that make.unique will be called on the column names before this matching is done. If row_names is a character string, the data frame row names will be stored in a column with the column name given by row_names. The row_names column can also be the primary key if pkey is the same as row_names.

If row_names is specified when calling read_table, then the resulting data frame will have row names installed from the column named in row_names. Note that the column named in row_names must match a column specified by what. The matching column will be removed from the data frame.

If types is not supplied, they will be computed from the classes and types of the columns of input.

write_table the final query status

read_table a data frame

The entire process is wrapped within a transcation. On failure at any point, the transaction will be rolled back and the database unaffected.

Also, write_table uses SQL INSERT statements and as such will be slow for large tables. You are much better off bulk loading data using the COPY command outside of R.

Timothy H. Keitt

copy_from

## Not run: 
# connect using defaults
createdb("rpgtesting")
connect("rpgtesting")
begin()
 
# write data frame contents
data(mtcars)
write_table(mtcars)

# make "cyl" primary key (will fail unique constraint)
write_table(mtcars, pkey = "cyl", overwrite = TRUE)

# also write row names to "id"
write_table(mtcars, row_names = "id", overwrite = TRUE)

# row names as primary key
write_table(mtcars, row_names = "id", pkey = "id", overwrite = TRUE)

# default R row names and only first 3 columns
read_table("mtcars", what = "mpg, cyl, disp", limit = 3)

# row names from column "id"
read_table("mtcars", row_names = "id", limit = 3)

# get row names from primary key
read_table("mtcars", pkey_to_row_names = TRUE, limit = 3)

#cleanup
rollback()
disconnect()
dropdb("rpgtesting")
## End(Not run)