org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint on persist() without ID?

Question

I'm getting an exception with Hibernate that makes absolutely no sense to me. I am using the persist() method to store a new, unattached entity without an ID (so it does not result ina detached entity passed exception). I even explicitely set the ID of the entity to null before passing it in. So as far as i understand, autoincrement will generate a new ID for the new entity to be saved (which is also mutated in the passed object for use after the method call). This is how it has always worked for me at least. However, something really strange is happening in this code and i cant figure out why, maybe its something minor. When i call this code:

    @Transactional
    public Settings createSettingsFor(Long id, Settings settings) {
        // Setting the ID explicitely to null here, to be sure it is not set before persist
        settings.setId(null);
        // Exception triggers here
        entityManager.persist(settings);
        entityManager.flush();
        // Attempting to link the newly created setting with another table entity manually, since i initially thought the error might be here but, but its caused before
        entityManager
            .createNativeQuery("UPDATE bots b WHERE b.id = :id SET b.settings_id = :settings_id")
            .setParameter("id", id)
            .setParameter("settings_id", settings.getId())
            .executeUpdate();
        return settings;
    }

i get the following exception:

org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "settings_pkey"
  Detail: Key (id)=(1) already exists.

Now, the thing is: There is indeed already an entry in the settings table with the ID 1, which is a default setting generated when the database is created. However, why is hibernate selecting an ID already in use to persist a new, unattached entity (without ID)? I thought it must be some odd caching thing, but i rebuild the app a dozen times now.

Furious Gamer · Accepted Answer · 2023-03-02 11:09:50Z

0

I figured it out. The issue was that the table schema for the "settings" table was generated with identity sequence provider using "START WITH 1":

ALTER TABLE settings ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME settings_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

Which apparently means that it will always force starting with 1, even if an entry already exists with ID 1.

answered Mar 2, 2023 at 11:09

Furious Gamer

5392 gold badges9 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint on persist() without ID?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related