I am in the process of switching databases for a Spring Boot project from MySQL to PostgreSQL. For the most part this hasn't been too painful, however I am finding that I am getting an exception I can't seem to sort out.
org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "r_i_UNIQUE"
Detail: Key (cola_id, colb_id)=(1234567, 12345) already exists.
The behaviour in MySQL when this occurred was to perform an update, which is what I need to occur in PostgreSQL as well.
Code wise, I search for an existing item by the
colb_id, the object is returned from my repository, is manipulated as required and then persisted using
It is on the
saveAndFlush that I get the exception.
My assumption is that Spring is seeing this as a new object and trying to do an insert rather than an update, but I am not understanding why this would occur as it worked as expected using MySQL.
How does PostgreSQL handle updates using saveAndFlush, and how can I get an update to apply correctly?