search

ankane / dbx

A fast, easy-to-use database library for R
Other
187 stars 15 forks source link

Upsert error in Postgres #28

Closed noamanemobidata closed 3 years ago

noamanemobidata commented 3 years ago

Hello! I try to reproduce the example : 

library(dbx)
library(RPostgres)
dbx <- dbxConnect(adapter="RPostgres", dbname=Sys.getenv('db'), 
                     host =Sys.getenv("host"),
                     user = Sys.getenv("uid"),
                     password = Sys.getenv("pwd"),
                     sslmode="require", options="-c search_path=m2",
                     port = 5432)

dbWriteTable(dbx, SQL("m2.test"),  data.frame(id=1:3, temperature=20:22), overwrite=T)

records <- data.frame(id=c(3, 4), temperature=c(20, 25))
dbxUpsert(dbx, SQL("m2.test"), records, where_cols=c("id"))

It returns this error

Error: Failed to fetch row: ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

noamanemobidata commented 3 years ago

Solved by adding a unique index in the table