How do I write data from R to PostgreSQL tables with an autoincrementing primary key?

Question

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 ); ``` 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) ``` Unfortunately, `dbWriteTable` throws an error: ``` ## 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" ## ) ``` The error message isn't completely clear, but I interpret this as R tryin…

Accepted Answer

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

In the `postgresqlWriteTable` in the `RPostgreSQL` package, you need to replace the line

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

with

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

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

How do I write data from R to PostgreSQL tables with an autoincrementing primary key?

Solution

Problem

Related problems