copy: Bulk read and write
In thk686/rpg: Easy Interface to Advanced PostgreSQL Features

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

Read from and write to a database using COPY

1
2
3

copy_from(what, psql_opts = "")

copy_to(x, tablename, schemaname = NULL, append = FALSE, psql_opts = "")

`what`	a table name or sql query string
`psql_opts`	passed directly to the psql command line
`x`	a data frame
`tablename`	name of table to create
`schemaname`	create table in this schema
`append`	if false, drop and receate table

These functions use the SQL COPY command and therefore are much faster than write_table and possibly read_table. These functions also call PostgreSQL's psql command from the command line and will fail if it is not found on the search path.

Because these functions shell out to psql you do not need an active connection. By specifying psql_opts you can connect to any database without affecting the active connection. If you do not specify psql_opts an attempt will be made to use the active connection information. If that fails, psql will use default connection settings.

These functions call read.csv and write.csv and so will suffer the same bandwidth limitations as those functions. I argue that is good enough. There is little point in reading and writing datasets too large for those functions in R. Better to bulk load using psql on the command line and then use cursor to read the data in small bits.

Timothy H. Keitt

set_default_password

## Not run: 
# example requires hflights
if (! require(hflights, quietly = TRUE))
 stop("This example requires the \'hflights\' package")

# big dataset
data(hflights)
dim(hflights)

system(paste("createdb rpgtesting"))

opts = paste("-d rpgtesting")
system.time(copy_to(hflights, psql_opts = opts))
system.time(invisible(copy_from("hflights", psql_opts = opts)))

connect("rpgtesting")
begin()

## Sloooowwwwwww
## system.time(write_table(hflights))
system.time(invisible(read_table("hflights")))

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