title: How do I write data from R to PostgreSQL tables with an autoincrementing primary key?
body: <p>I have a table in a PostgreSQL database that has a <code>BIGSERIAL</code> auto-incrementing primary key.  Recreate it using:</p>
  
  <pre><code>CREATE TABLE foo
  (
    "Id" bigserial PRIMARY KEY,
    "SomeData" text NOT NULL
  );
  </code></pre>
  
  <p>I want to append some data to this table from R via the <code>RPostgreSQL</code> package.  In R, the data doesn't include the <code>Id</code> column because I want the database to generate those value.</p>
  
  <pre><code>dfr &lt;- data.frame(SomeData = letters)
  </code></pre>
  
  <p>Here's the code I used to try and write the data:</p>
  
  <pre><code>library(RPostgreSQL)
  conn &lt;- dbConnect(
    "PostgreSQL", 
    user     = "yourname", 
    password = "your password",
    dbname   = "test"
  )
  dbWriteTable(conn, "foo", dfr, append = TRUE, row.names = FALSE)
  dbDisconnect(conn)
  </code></pre>
  
  <p>Unfortunately, <code>dbWriteTable</code> throws an error:</p>
  
  <pre><code>## 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"
  ## )
  </code></pre>
  
  <p>The error message isn't completely clear, but I interpret this as R trying to pass the contents of the <code>SomeData</code> column to the first column in the database (which is <code>Id</code>).</p>
  
  <p>How should I be passing the data to PostgreSQL so that the <code>Id</code> column is auto-generated?</p>