write data to table using RPostgreSQL without altering structure

Question

A thing I ran into using the RPostgreSQL package is that dbWriteTable(… overwrite=TRUE) seems to destroy existing table structure (datatypes and constraints), and dbRemoveTable() is equivalent to DROP table.

So I’ve used:

ltvTable <- "the_table_to_use"
dfLTV <- dataframe(x,y,z)
sql_truncate <- paste("TRUNCATE ", ltvTable) ##unconditional DELETE FROM…
res <- dbSendQuery(conn=con, statement=sql_truncate)
dbWriteTable(conn=con, name=ltvTable, value=dfLTV, row.names=FALSE, append=TRUE)

Is the TRUNCATE step necessary, or is there a dbWriteTable method that overwrites just the content not the structure?

I experience different behaviour from the answer offered by Manura Omal to How we can write data to a postgres DB table using R?, as overwrite=TRUE does not appear to truncate first.

I'm using: RPostgreSQL 0.4-1; PostgreSQL 9.4

best wishes - JS

Michał Okulewicz · Accepted Answer · 2017-10-19 11:08:44Z

3

As far as I know overwrite=T does 3 things:

DROPs the table
CREATES the table
fills table with new data

So if you want to preserve the structure, then you need the Truncate step. Different behaviour might be caused by existence or non-existence of foreign keys preventing the DROP table step.

answered Oct 19, 2017 at 11:08

Michał Okulewicz

1592 silver badges8 bronze badges

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

Collectives™ on Stack Overflow

write data to table using RPostgreSQL without altering structure

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related