[R-sig-DB] RPostgreSQL Row Inserts on Remote Servers

[McGehee, Robert]

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}}