upsert not upserting 😢

[kubussku]

Tried using example approach described as well as mine similar one but either return "ON Conflict" error message - this happens only to postgres. (ver 14 , hosted on google cloud). And works as insert for mysql (ver 8.0 host on gc)

For MySQL instance upsert works like insert ignoring matching cases. Codes below

Given example below is which i created but the result is the same when using dbx github example

con <- DBI::dbConnect(
  RPostgres::Postgres(),
  dbname = "database",
  host = r'{127.0.0.1}',
  port = 5432,
  user = 'admin',
  password = 'password')

# below connection works the same as above one, returns same error message when trying to upsert
#db <- dbx::dbxConnect(adapter="postgres",
#                      user = 'admin',
#                      dbname="database",
#                      password = 'password')

tab1 = data.frame(#id = as.integer(c(1,2,3,4,5)),
  #row_names = as.integer(1:5),
  names = as.character(c("jakub",'joe','daryl','barba','gwen')),
  age = as.numeric(c(30.2,32.1,35.6,25,30.33)))

tab2 = rbind(tab1, data.frame(names = 'jenny',age = 38.33))
tab2[tab2$names == 'jakub',]$age = 32.333

DBI::dbCreateTable(con, 'testing', tab1)
DBI::dbReadTable(con, 'testing')
DBI::dbAppendTable(conn = con,name = 'testing',value = tab1)

dbx::dbxUpsert(conn = con, table = 'testing',records = tab2,where_cols = c("names"))

> DBI::dbReadTable(con, 'testing')
  names   age
1 jakub 30.20
2   joe 32.10
3 daryl 35.60
4 barba 25.00
5  gwen 30.33

> tab2
  names   age
1 jakub 30.20
2   joe 32.10
3 daryl 35.60
4 barba 25.00
5  gwen 30.33
6 jenny 38.33

> dbx::dbxUpsert(conn = con, table = 'testing',records = tab2,where_cols = c("names"))
Error: Failed to fetch row: ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

MYSQL 8.0 google cloud sql

> # MY SQL database tests
> con = DBI::dbConnect(RMySQL::MySQL(),
+                             dbname="database",
+                             host="127.0.0.1",
+                             port=3306,
+                             user='admin',
+                             password='password')
> 
> con
<MySQLConnection:0,1>
> DBI::dbCreateTable(con, 'testing', tab1)
Error in .local(conn, statement, ...) : 
  could not run statement: Table 'testing' already exists
> DBI::dbReadTable(con, 'testing')
[1] names age  
<0 wierszy> (lub 'row.names' o zerowej długości)
> DBI::dbAppendTable(conn = con,name = 'testing',value = tab1)
Error in .local(conn, statement, ...) : 
  unused argument (params = list(c("jakub", "joe", "daryl", "barba", "gwen"), c(30.2, 32.1, 35.6, 25, 30.33)))
> 
> dbx::dbxUpsert(conn = con, table = 'testing',records = tab2,where_cols = c("names"))
> DBI::dbReadTable(con, 'testing')
  names   age
1 jakub 30.20
2   joe 32.10
3 daryl 35.60
4 barba 25.00
5  gwen 30.33
6 jenny 38.33
> dbx::dbxUpsert(conn = con, table = 'testing',records = tab2,where_cols = c("names"))
> DBI::dbReadTable(con, 'testing')
   names   age
1  jakub 30.20
2    joe 32.10
3  daryl 35.60
4  barba 25.00
5   gwen 30.33
6  jenny 38.33
7  jakub 30.20
8    joe 32.10
9  daryl 35.60
10 barba 25.00
11  gwen 30.33
12 jenny 38.33

[kubussku]

Using rpostgis to add PK solves the issue.

rpostgis::dbAddKey(conn = con,name = 'testing',colname = c('names'))

[ankane]

Hi @kubussku, upsert requires a unique index on the columns in where_cols (which could be a PK, but doesn't have to be).