Rilcon42 Rilcon42 - 3 months ago 8
R Question

postgresql database not updating based on stored data.frame

According to the man page on

dbWriteTable
(using library
RPostgreSQL
) it should overwrite the values in the database with the values in the dataframe. Anyone have any ideas why the database isnt updating?

> head(df_postgres)
time oask hask lask cask obid hbid lbid cbid volume rsi3 signal price_change_dir change_ask
1 2002-05-06 20:00:00 0.9184 0.9184 0.9181 0.9184 0.9181 0.9181 0.9181 0.9181 1 NA NA NA NA
2 2002-05-07 20:00:00 0.9155 0.9155 0.9152 0.9155 0.9152 0.9152 0.9152 0.9152 1 NA NA -1 -0.0029
3 2002-05-08 20:00:00 0.9045 0.9045 0.9042 0.9045 0.9042 0.9042 0.9042 0.9042 1 NA NA -1 -0.0110
4 2002-05-09 20:00:00 0.9095 0.9095 0.9092 0.9095 0.9092 0.9092 0.9092 0.9092 1 NA NA 1 0.0050
5 2002-05-10 20:00:00 0.9145 0.9145 0.9135 0.9145 0.9135 0.9135 0.9135 0.9135 1 NA NA 1 0.0050
6 2002-05-10 22:00:00 0.9146 0.9146 0.9136 0.9146 0.9136 0.9136 0.9136 0.9136 1 NA NA 1 0.0001
> dbWriteTable(con, 'forex_data',value = df_postgres, append = TRUE, row.names = FALSE)
[1] TRUE
>
> df_postgres<-getTable("forex_data")
> head(df_postgres)
time oask hask lask cask obid hbid lbid cbid volume rsi3 signal price_change_dir change_ask
1 2002-05-06 20:00:00 0.9184 0.9184 0.9181 0.9184 0.9181 0.9181 0.9181 0.9181 1 NA NA NA NA
2 2002-05-07 20:00:00 0.9155 0.9155 0.9152 0.9155 0.9152 0.9152 0.9152 0.9152 1 NA NA NA NA
3 2002-05-08 20:00:00 0.9045 0.9045 0.9042 0.9045 0.9042 0.9042 0.9042 0.9042 1 NA NA NA NA
4 2002-05-09 20:00:00 0.9095 0.9095 0.9092 0.9095 0.9092 0.9092 0.9092 0.9092 1 NA NA NA NA
5 2002-05-10 20:00:00 0.9145 0.9145 0.9135 0.9145 0.9135 0.9135 0.9135 0.9135 1 NA NA NA NA
6 2002-05-10 22:00:00 0.9146 0.9146 0.9136 0.9146 0.9136 0.9136 0.9136 0.9136 1 NA NA NA NA

getTable<-function(x){
df_postgres<-NULL
if(dbExistsTable(con, x))
df_postgres <- dbGetQuery(con, paste0("SELECT * from ",x))
else {cat("error retrieving data")}
df_postgres
}

Answer

To explicitly overwrite a table with dbWriteTable() use the overwrite argument (FALSE by default):

 dbWriteTable(con, 'forex_data', value = df_postgres, overwrite = TRUE, row.names = FALSE)