如何使用自动递增的主键将数据从R写入PostgreSQL表？ [英] How do I write data from R to PostgreSQL tables with an autoincrementing primary key?

问题描述

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

I have a table in a PostgreSQL database that has a BIGSERIAL auto-incrementing primary key. Recreate it using:

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

我想通过 RPostgreSQL 软件包。在R中，数据不包含 Id 列，因为我希望数据库生成这些值。

I want to append some data to this table from R via the RPostgreSQL package. In R, the data doesn't include the Id column because I want the database to generate those value.

dfr <- data.frame(SomeData = letters)

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

Here's the code I used to try and write the data:

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 ）。

The error message isn't completely clear, but I interpret this as R trying to pass the contents of the SomeData column to the first column in the database (which is Id).

应如何将数据传递到PostgreSQL，以便自动生成 Id 列？

How should I be passing the data to PostgreSQL so that the Id column is auto-generated?

推荐答案

从hrbrmstr注释中的线程中，我找到了

From the thread in hrbrmstr's comment, I found a hack to make this work.

在<$ c $ postgresqlWriteTable 中的 RPostgreSQL 包，您需要替换行

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

with

sql4 <- paste(
  "COPY ", 
  postgresqlTableRef(name), 
  "(", 
  paste(postgresqlQuoteId(names(value)), collapse = ","), 
  ") FROM STDIN"
)

请注意变量的引用（原始hack中不包括）传递区分大小写的列名是必需的。

Note that the quoting of variables (not included in the original hack) is necessary to pass case-sensitive column names.

以下是一个脚本来做到这一点：

Here's a script to do that:

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

这篇关于如何使用自动递增的主键将数据从R写入PostgreSQL表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！