RPostgreSQL - import dataframe into a table

Question

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 · Accepted Answer · 2015-11-10 17:53:24Z

15

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)

answered Nov 10, 2015 at 17:53

Dean MacGregor

20k10 gold badges57 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Chanti Over a year ago

@ Dean Mac Gregor: thank you very much. it worked very well. your one line solution saved lot my time and effort. thank you

Chanti Over a year ago

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 Over a year ago

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.

Dean MacGregor Over a year ago

@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.

Collectives™ on Stack Overflow

RPostgreSQL - import dataframe into a table

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related