6

I want to export a complete dataframe into a table which is already created in a database(postgresql) and contains the similar data.

I found few questions explaining about dbwrite table (....overwrite = TRUE), i don't want to overwrite the data which is already present in my table. i just want to update my table with the dataframe from r console.

can someone let me know how can i do this..

something like this

dbInsertTable(con, df, tablename = "MyTable")
Dean MacGregor
  • 11,847
  • 9
  • 34
  • 72
Chanti
  • 525
  • 1
  • 5
  • 15

1 Answers1

14

You'll need dbWriteTable

Assuming you don't use row names in your data frame you'd do

dbWriteTable(con, "MyTable", df, row.names=FALSE, append=TRUE)

if you want your row names from the df to be a column in your database table then you'd set that option to TRUE. If your table is in a schema other than the public schema then you'd do c('myschema', 'MyTable') instead of the intuitive 'myschema.MyTable'. Also, the columns of your dataframe need to be in the same order as the columns in your database's table. It matches based on order not name.

As an aside, you probably shouldn't use capital letters in your postgres table or column names because then you need to quote them. If you're really using capital letters then you'd need something like dbWriteTable(con, '"MyTable"', df, row.names=FALSE, append=TRUE)

Dean MacGregor
  • 11,847
  • 9
  • 34
  • 72
  • @ Dean Mac Gregor: thank you very much. it worked very well. your one line solution saved lot my time and effort. thank you – Chanti Nov 11 '15 at 09:59
  • Mac Gregor: Thank you very much once again for the above solution. I have a small question. In **"mytable"** i have 4 columns and for one column i am using unique key constraint. while updating the table from r console with the above solution you suggested I am facing a small error and i know the reason for that(unique key constraint). – Chanti Dec 09 '15 at 09:51
  • for example i have a unitnumber 12345 in column **un_number** in my data frame. the same column **un_number** in **mytable** already contains unitnumber 12345. so my question is, incase if i have a duplicate unitnumber in dataframe along with other original numbers - is it possible to stop only that number instead of stoppping complete dataframe from updating because of unique constarint. – Chanti Dec 09 '15 at 09:52
  • @Chanti Not that I'm aware of. You'll have to either filter df or delete the rows from mytable. You could also dbwritetable to a temp table and then do an upsert query from temp table to permanent table. – Dean MacGregor Dec 09 '15 at 13:44