How to write to tables with an auto-incrementing primary key?

[Richard Cotton]

I want to write to a table in a PostgreSQL database that has a BIGSERIAL auto-incrementing primary key.  Rather than creating key values in R and passing them to PostgreSQL, I want PostgreSQL to handle that itself.

This problem is cross-posted from Stack Overflow where you can find code to reproduce the issue.  A comment there pointed me to this group.

An older post on this group contains a patch to make postgresqlWriteTable only write columns that exist in the data frame.  The thread got rather long and confused, and while it was implied that the patch had been committed, it doesn't seem to have made it into mainline RPostgreSQL.  The thread also hinted that there may be better ways deal with this, but they weren't explicitly mentioned.

I believe that the ability to let PostgreSQL handle auto-incrementing primary keys (and the more general feature of only copying some columns of a table from R to the database) is very useful.  Is my enhanced version of the original path worthy of inclusion into RPostgreSQL, or is there a better way of doing things?  Either way, I think that this issue is important enough to revisit.

[Richard Cotton]

I was sent a private response to the problem hinting that it may be solvable by passing SQL NULL for that column.  I'm not sure how to do this though.  For the example of a table, "foo" defined as:

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

and an R data frame

dfr <- data.frame(SomeData = letters)

I figure I ought to be able to pass a field.types argument to dbWriteTable and have it passed to postgresqlWriteTable.  I tried

dbWriteTable(conn, "foo", dfr, append = TRUE, row.names = FALSE, field.types = list(Id = NULL, SomeData = "text"))
dbWriteTable(conn, "foo", dfr, append = TRUE, row.names = FALSE, field.types = list(Id = "NULL", SomeData = "text"))
dbWriteTable(conn, "foo", dfr, append = TRUE, row.names = FALSE, field.types = list(Id = RPostgreSQL:::.PostgreSQL.NA.string, SomeData = "text"))

none of which worked.  That is, each time I got the error message:

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"

Is passing a null column a viable possibility?  If so, how do I do that?

[Jonathan Adams]

Richard,

I don't know if you are still having trouble with this, but I too was having the same problem. I dug through the code and created my own fix for this issue before discovering the "older post" you referenced. The implementation seems to handle auto-incrementing keys perfectly, as well as only inserting NULL or DEFAULT values to columns that are not in the data frame being inserted. You can find the complete updated package at my GitHub repo: https://github.com/PieceMaker/RPostgreSQL. You will most likely be interested in the branch titled "patch". You can install it using Hadley Wickham's "devtools" package and running the following command:

install_github('PieceMaker/RPostgreSQL', ref = 'patch')

Once it is installed, all you have to do is run your original "dbWriteTable" command with the argument "match.cols = TRUE". It will automatically cross reference the columns of the data frame being inserted with the columns of the destination table. As long as they all exist in the destination, it should insert without a problem.