Database.Esqueleto.PostgreSQL.upsertBy syntax error in generated SQL

[tim2CF]

Error was found in esqueleto 3.5.3 from hackage. To reproduce, you need to apply expression similar to

import qualified Database.Esqueleto.PostgreSQL as Psql
Psql.upsertBy                                          
  (UniqueUser email)                          
  userData                                                 
  []

And then error will look like

       uncaught exception: SomeAsyncException                                                  
       ExceptionInLinkedThread ThreadId 3880 (SqlError {sqlState = "42601", sqlExecStatus = Fat
alError, sqlErrorMsg = "syntax error at or near \"WHERE\"", sqlErrorDetail = "", sqlErrorHint =
 ""})                                                                                          

Expected behaviour - atomic insert in case where Unique key was not used yet, and ignore (no update) in case Unique key has been already used.

There is a workaround to avoid such error and get expected behaviour - you need to do redundant update of Unique key like this:

import qualified Database.Esqueleto.PostgreSQL as Psql
Psql.upsertBy                                          
  (UniqueUser email)                          
  userData                                                 
  [UserEmail =. val email]

But in my opinion it should work as expected without any workarounds.

[belevy]

This seems like it should be doable, looks like persistUniqueToFieldNames and persistUniqueToValues should allow for us to generate a default where clause. I am not particularly familiar with the expected behavior here though. Is there ever a reason the where clause should be anything other than matching the Unique rec value?