[R-sig-DB] RPostgreSQL Row Inserts on Remote Servers
McGehee, Robert
Robert@McGehee @end|ng |rom geodec@p|t@|@com
Tue Jul 20 17:37:27 CEST 2010
Hello,
An issue discussed before on this list server is difficulty using
dbWriteTable from the RPostgreSQL/DBI package to insert rows when
either:
1) the R client and PostgreSQL server are on different computers (or at
least don't share a common filesystem), and thus bulk copy is
unavailable.
2) the postgres user does not have read permission for the user's file
(See https://stat.ethz.ch/pipermail/r-sig-db/2009q4/000741.html, for
example)
To address this, I wrote a helper function called dbInsert() that some
of you may find useful. The function converts a data frame into a single
INSERT query transaction that inserts the data frame into an existing
table. My hope is that this is a relatively fast way to insert rows when
bulk copy is unavailable.
If any of you have already discovered a faster way to insert data
without using bulk copy, or find ways of improving this code, please
share. I'm also CC:ing the RPostgreSQL package maintainer in case he
thinks this functionality should be merged into the postgresWriteTable
function for when bulk copy is not possible.
Cheers,
Robert
dbInsert <- function(con, name, value, row.names = TRUE, ...) {
xx <- dbSendQuery(con, paste("select * from", name, "LIMIT 1;"))
cols <- dbColumnInfo(xx)$name
dbClearResult(xx)
if (row.names) {
if (!"row_names" %in% cols) stop("row_names column missing from
", sQuote(name))
value[["row_names", exact=TRUE]] <- rownames(value)
}
if (length(setdiff(names(value), cols)))
stop("names of 'value' do not match columns of ", sQuote(name))
cdt <- which(sapply(value, inherits, c("Date", "POSIXt")))
ctxt <- which(sapply(value, postgresqlDataType)=="text")
for (i in cdt)
value[[i]] <- ifelse(is.na(value[[i]]), "NULL",
sQuote(format(value[[i]])))
for (i in setdiff(ctxt, cdt))
value[[i]] <- ifelse(is.na(value[[i]]), "NULL",
sQuote(value[[i]]))
m <- as.matrix(value)
class(m) <- "character"
m[is.na(m)] <- "NULL"
q1 <- paste("BEGIN; INSERT INTO", name, "(", paste(names(value),
collapse=", "), ") VALUES")
q2 <- apply(m, 1, function(x) paste("(", paste(x, collapse=","),
")", sep=""))
q3 <- "; COMMIT;"
qry <- paste(q1, paste(q2, collapse=","), q3)
dbGetQuery(con, qry)
}
Robert McGehee, CFA
Geode Capital Management, LLC
One Post Office Square, 28th Floor | Boston, MA | 02109
Tel: 617/392-8396 Fax:617/476-6389
mailto:robert.mcgehee using geodecapital.com
This e-mail, and any attachments hereto, are intended fo...{{dropped:12}}
More information about the R-sig-DB
mailing list