write_table: PostgreSQL data frame IO

Description

Reads and writes table to and from database

Usage

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)

Arguments

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

Value

write_table the final query status

read_table a data frame

Details

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.

Examples

Run this code

# 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")
# }
# NOT RUN {
# }