Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Error in postgresqlpqExec(new.con, sql4) : RS-DBI driver: (could not Retrieve the result : ERROR: syntax error at or near "STDIN" LINE 1: COPY "table_1" FROM STDIN
- body_lines <- deparse(body(RPostgreSQL::postgresqlWriteTable))
- new_body_lines <- sub(
- 'postgresqlTableRef(name), "FROM STDIN")',
- 'postgresqlTableRef(name), "(", paste(shQuote(names(value)), collapse = ","), ") FROM STDIN")',
- body_lines,
- fixed = TRUE
- )
- fn <- RPostgreSQL::postgresqlWriteTable
- body(fn) <- parse(text = new_body_lines)
- while("RPostgreSQL" %in% search()) detach("package:RPostgreSQL")
- assignInNamespace("postgresqlWriteTable", fn, "RPostgreSQL")
- Error in postgresqlExecStatement(conn, statement, ...) :
- RS-DBI driver: (could not Retrieve the result : ERROR: column "id" does not exist in table_1
- )
- creating NAs/NULLs for for fields of table that are missing in your df
- Error in postgresqlExecStatement(conn, statement, ...) :
- RS-DBI driver: (could not Retrieve the result : ERROR: column "id" does not exist in table_1
- )
- ## connect to the DB
- library('RPostgreSQL'); ## loads DBI automatically
- drv <- dbDriver('PostgreSQL');
- con <- dbConnect(drv,host=...,port=...,dbname=...,user=...,password=...);
- ## define helper functions
- createEmptyTable <- function(con,tn,df) {
- sql <- paste0("create table "",tn,"" (",paste0(collapse=',','"',names(df),'" ',sapply(df[0,],postgresqlDataType)),");");
- dbSendQuery(con,sql);
- invisible();
- };
- insertBatch <- function(con,tn,df,size=100L) {
- if (nrow(df)==0L) return(invisible());
- cnt <- (nrow(df)-1L)%/%size+1L;
- for (i in seq(0L,len=cnt)) {
- sql <- paste0("insert into "",tn,"" values (",do.call(paste,c(sep=',',collapse='),(',lapply(df[seq(i*size+1L,min(nrow(df),(i+1L)*size)),],shQuote))),");");
- dbSendQuery(con,sql);
- };
- invisible();
- };
- ## generate test data
- NC <- 1e2L; NR <- 1e3L; df <- as.data.frame(replicate(NC,runif(NR)));
- ## run it
- tn <- 't1';
- dbRemoveTable(con,tn);
- createEmptyTable(con,tn,df);
- insertBatch(con,tn,df);
- res <- dbReadTable(con,tn);
- all.equal(df,res);
- ## [1] TRUE
- insertBatch <- function(con,tn,df,size=100L) {
- if (nrow(df)==0L) return(invisible());
- cnt <- (nrow(df)-1L)%/%size+1L;
- for (i in seq(0L,len=cnt)) {
- sql <- paste0("insert into "",tn,"" values (",do.call(paste,c(sep=',',collapse='),(',lapply(df[seq(i*size+1L,min(nrow(df),(i+1L)*size)),],shQuote))),");");
- dbSendQuery(con,sql);
- };
- invisible();
Add Comment
Please, Sign In to add comment