如何在R中将数据写入具有自动递增主键的PostgreSQL表？

Question

我有一个在PostgreSQL数据库中的表，其中有一个BIGSERIAL自增主键。 使用以下命令重新创建它：

CREATE TABLE foo
(
  "Id" bigserial PRIMARY KEY,
  "SomeData" text NOT NULL
);

我希望使用RPostgreSQL包从R中将一些数据添加到此表格中。在R中，数据不包括Id列，因为我希望数据库生成这些值。

dfr <- data.frame(SomeData = letters)

这是我用来尝试写数据的代码：

library(RPostgreSQL)
conn <- dbConnect(
  "PostgreSQL", 
  user     = "yourname", 
  password = "your password",
  dbname   = "test"
)
dbWriteTable(conn, "foo", dfr, append = TRUE, row.names = FALSE)
dbDisconnect(conn)

不幸的是，dbWriteTable 抛出了一个错误：

## Error in postgresqlgetResult(new.con) : 
##   RS-DBI driver: (could not Retrieve the result : ERROR:  invalid input syntax for integer: "a"
## CONTEXT:  COPY foo, line 1, column Id: "a"
## )

错误信息并不十分清晰，但我理解为R试图将SomeData列的内容传递给数据库中的第一列（即Id）。

我应该如何将数据传递给PostgreSQL以使Id列自动生成？

- Richie Cotton

---

我不相信RPostgreSQL分发(长线程，抱歉，但它涉及到你的问题)中提到的这个补丁已经实施了。似乎dbWriteTable正在发出COPY, 但没有办法指定字段名称(这就导致了问题)。在dbWriteTable中只有不足够的智能来查看是否有一个SERIAL字段。caroline中的dbWriteTable2有一个hack-ish解决方法(通过使用自己的表格id字段)。我可能会选择制作一个使用带有字段名称的COPY版本。 - hrbrmstr

Answer 1

从hrbrmstr的评论中的帖子中，我找到了一种方法来使它起作用。

在RPostgreSQL包的postgresqlWriteTable中，你需要替换这行：

sql4 <- paste("COPY", postgresqlTableRef(name), "FROM STDIN")

使用

sql4 <- paste(
  "COPY ", 
  postgresqlTableRef(name), 
  "(", 
  paste(postgresqlQuoteId(names(value)), collapse = ","), 
  ") 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")

- Richie Cotton

---

这太棒了，对我来说完美无缺。老实说，我真不知道为什么一开始它就做不到这个。 - JaredL

嗨，Richie - 如果我理解正确，脚本会自动应用更改到RPostgreSQL包吗？我执行了你的脚本，但仍然得到相同的错误，如果不是非常相似的话。也许我的用例不同... - jgozal

你可以下载软件包源代码，编辑 R 函数，然后安装修改后的版本，这样可能更容易些。 - Richie Cotton

我在考虑这样做，我正在尝试加载一个工作区，其中函数已经被修改，但如果这不起作用，我将直接从源代码中进行修改。 - jgozal

@RichieCotton 当我输入 RPostgreSQL::postgresqlWriteTable 时，它显示了函数的新版本，但是我遇到了以下错误... Error in postgresqlpqExec(new.con, sql4) : RS-DBI driver: (could not Retrieve the result : ERROR: syntax error at or near "STDIN" LINE 1: COPY "female_view_new3" FROM STDIN - Gaurav

Answer 2

今天我遇到了一个非常类似的问题，在尝试不同的方法时偶然发现了这个帖子。截至本文撰写（2018年2月12日），看起来上面推荐的修补程序已经被实现到最新版的RPostgreSQL::postgresqlWriteTable中，但我仍然一直收到错误提示，指示R分配给我的新行的主键在源数据表中重复。

最终，我实现了一个解决方法，在 R 中生成递增的主键，以附加到我的插入数据中，更新我的postgreSQL数据库中的源表。对于我的目的，我只需要一次向我的表中插入一条记录，我无法想象这是插入批量要求序列递增主键记录的最佳解决方案。可预测的是，“在脚本中省略'append=TRUE'”时会抛出“表my_table存在于数据库中：放弃assignTable”的错误; 然而，即使使用上述代码修补程序，此选项也没有自动分配递增的主键，正如我所希望的那样。

drv <- dbDriver("PostgreSQL")
localdb <- dbConnect(drv, dbname= 'MyDatabase',
                      host= 'localhost',
                    port = 5432,
                    user = 'postgres',
                    password= 'MyPassword')

KeyPlusOne <- sum(dbGetQuery(localdb, "SELECT count(*) FROM my_table"),1)
NewRecord <- t(c(KeyPlusOne, 'Var1','Var2','Var3','Var4'))
NewRecord <- as.data.frame(NewRecord)
NewRecord <- setNames(KeyPlusOne, c("PK","VarName1","VarName2","VarName3","VarName4"))

postgresqlWriteTable(localdb, "my_table", NewRecord, append=TRUE, row.names=FALSE)

- Paul Sochacki

---

你使用 setNames() 的最后一行会导致错误： 'names' attribute [5] must be the same length as the vector [1]``` - MokeEire