dbReadTable-methods: Convenience functions for Importing/Exporting DBMS tables

In RPostgreSQL: R Interface to the 'PostgreSQL' Database System

Convenience functions for Importing/Exporting DBMS tables

Description

These functions mimic their R/S-Plus counterpart get, assign, exists, remove, and objects, except that they generate code that gets remotely executed in a database engine.

Value

A data.frame in the case of dbReadTable; otherwise a logical indicating whether the operation was successful.

Methods

conn

an PostgreSQLConnection database connection object.

name

a character string specifying a table name.

value

a data.frame (or coercible to data.frame).

When the value is a character string, it is assumed to be a file name containing the data to be loaded; The implementation is INCOMPLETE and should not be used in current state.

row.names

UNTESTED;

in the case of dbReadTable, this argument can be a string or an index specifying the column in the DBMS table to be used as row.names in the output data.frame (a NULL, "", or 0 specifies that no column should be used as row.names in the output).

In the case of dbWriteTable, this argument should be a logical specifying whether the row.names should be output to the output DBMS table; if TRUE, an extra field whose name will be whatever the R/S-Plus identifier "row.names" maps to the DBMS (see make.db.names).

overwrite

a logical specifying whether to overwrite an existing table or not. Its default is FALSE.

append

a logical specifying whether to append to an existing table in the DBMS. Its default is FALSE.

allow.keywords

dbWriteTable accepts a logical allow.keywords to allow or prevent PostgreSQL reserved identifiers to be used as column names. By default it is FALSE.

dots

optional arguments.

When dbWriteTable is used to import data from a file, you may optionally specify header=, row.names=, col.names=, sep=, eol=, field.types=, skip=, and quote=. NOT FULLY IMPLEMENTED YET.

header is a logical indicating whether the first data line (but see skip) has a header or not. If missing, it value is determined following read.table convention, namely, it is set to TRUE if and only if the first row has one fewer field that the number of columns.

row.names is a logical to specify whether the first column is a set of row names. If missing its default follows the read.table convention.

col.names a character vector with column names; column names are quoted to work as SQL identifiers. Thus, the column names are case sensitive and make.db.names will NOT be used here.

sep= specifies the field separator, and its default is ','.

eol= specifies the end-of-line delimiter, and its default is '\n'.

skip specifies number of lines to skip before reading the data, and it defaults to 0.

field.types is a list of named field SQL types where names(field.types) provide the new table's column names (if missing, field types are inferred using dbDataType).

Note

dbWriteTable creates additional column in the database, while dbReadTable reads that column by default. So, it is not symmetrical in its current implementation. the backend raw_names may change in future versions.

References

See the Database Interface definition document DBI.pdf in the base directory of this package or https://cran.r-project.org/package=DBI.

See Also

PostgreSQL, isSQLKeyword, dbDriver, dbConnect, dbSendQuery, dbGetQuery, fetch, dbCommit, dbGetInfo, dbListTables, dbReadTable.

Examples

## Not run: 
conn <- dbConnect("PostgreSQL", dbname = "wireless")
if(dbExistsTable(conn, "frame_fuel")){
   dbRemoveTable(conn, "frame_fuel")
   dbWriteTable(conn, "frame_fuel", fuel.frame)
}
if(dbExistsTable(conn, "RESULTS")){
   dbWriteTable(conn, "RESULTS", results2000, append = T)
else
   dbWriteTable(conn, "RESULTS", results2000)
}

## End(Not run)

RPostgreSQL documentation built on Oct. 4, 2024, 1:13 a.m.