如何从R中向PostgreSQL编写表格？

Question

目前，在PostgreSQL表中插入数据，我必须先创建一个空表，然后使用insert into table values ...，还要将数据帧折叠成一个包含所有值的单个字符串。 对于大型数据框架，这并不起作用。

dbWtriteTable()在PostgreSQL上不起作用，并显示以下错误...

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

我尝试了以下hack，这是之前类似问题的答案中提供的建议。这是链接... 如何在R中写入数据到PostgreSQL表格并使用自增主键？

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")

这个黑客方法对我仍然不起作用。postgresqlWriteTable（）抛出完全相同的错误...问题到底在哪里？

作为替代，我尝试使用caroline包中的dbWriteTable2（）。但它会抛出另一个错误...

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
)

有没有其他方法可以直接将大型数据框写入PostgreSQL表中？

- Gaurav

---

我们在这里谈论的是多大规模？我刚刚成功地将一个约800MB大小、10,000x1,000的数据框架从R写入到PostgreSQL表中，使用了RPostgresQL::dbWriteTable()函数。这花费了一些时间（我想接近一个小时），但它确实起作用了。 - bgoldst

@bgoldst 我的意思是说，对于大量数据，insert into方法会失败...而即便是小量数据（1x1 df），dbWriteTable()也会失败并提示错误。 - Gaurav

Answer 1

好的，我不确定为什么dbWriteTable()会失败；可能存在某种版本/协议不匹配。如果可能的话，您可以尝试安装最新版本的R、RPostgreSQL包，并升级系统上的PostgreSQL服务器。

关于insert into解决方法在处理大数据时失败的问题，在IT世界中通常采用的做法是所谓的批处理或批量处理。基本上，您将数据分成较小的块，并逐个发送每个块。

作为一个随机的例子，几年前我编写了一些Java代码，从一个受限制只能提供1000条记录的HR LDAP服务器中查询员工信息。因此，我不得不编写一个循环来不断发送相同的请求（使用某种奇怪的基于cookie的机制跟踪查询状态），并将记录累积到本地数据库中，直到服务器报告查询完成为止。

这里有一些手动构建SQL以创建基于给定数据框的空表的代码，并使用参数化批量大小将数据框的内容插入表中。它主要是围绕调用paste()来构建SQL字符串和dbSendQuery()来发送实际查询而构建的。我还使用postgresqlDataType()来创建表。

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

请注意，我没有像 dbWriteTable()那样在数据库表前加上row.names列。这个函数总是包括这样一列（而且似乎没有任何方法可以防止它）。

- bgoldst

---

关于您的最后一条注释：dbWriteTable(..., row.names=F) - webb

Answer 2

在我按照这个例子进行操作时，也遇到了同样的错误。

对我而言，解决方法是：

dbWriteTable(con, "cartable", value = df, overwrite = T, append = F, row.names = FALSE)

我在pgAdmin中配置了一个名为“cartable”的表。因此存在一个空表，我需要用值覆盖该表。

- heiko

Answer 3

因此，之前给出的批处理答案是99.99%正确的。但是，在Windows上它不起作用，因为在“insertBatch”函数中需要一个微小的参数。

“shQuote”函数需要一个参数类型=“cmd2”才能正常工作。

然而，要在那里添加一个参数，您需要这个答案：

[https://dev59.com/Ymw15IYBdhLWcg3wGn9c][1]

因此，新的“insertBatch”函数变为：

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,type = 'cmd2'))),");");
    dbSendQuery(con,sql);
  };
  invisible();
};

- Rahul Jakhmola