table-io: PostgreSQL data frame IO

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

Description

Reads and writes table to and from database

Usage

1
2
3
4
5
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

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

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.

Value

write_table the final query status

read_table a data frame

Note

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.

Author(s)

Timothy H. Keitt

See Also

copy_from

Examples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
## 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)

thk686/rpg documentation built on Sept. 5, 2019, 11:13 p.m.