How to sync id on hibernate and postgres?

user_10254976 0
Viewed 158

Maybe someone faced the same situation? In my project I have two services. I create database tables using liquebase. When creating a table of permissions, I immediately enter 4 values ​​there using inserts. and everything is buzzing. So, now in the second service (admin panel) I created an apish that allows you to create permissions. When I try to make a record in the table of permissions, in which there are already 4 records, I get an error ERROR: duplicate key value violates unique constraint "permissions_pkey". The error occurs if I make a request 5 times, the record is added for the 5th time. Thanks in advance)

<insert tableName="permissions">
  <column name="id" value="1"/>
  <column name="name" value="USERS_READ"/>
</insert>
<insert tableName="permissions">
  <column name="id" value="2"/>
  <column name="name" value="USERS_GET"/>
</insert>
<insert tableName="permissions">
  <column name="id" value="3"/>
  <column name="name" value="USERS_CREATE"/>
</insert>
<insert tableName="permissions">
  <column name="id" value="4"/>
  <column name="name" value="USERS_DELETE"/>
</insert>

public class Permission {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id; 
}
hibernate spring java
2 Answers

If it's an identity column, you don't have to provide values for the id column in the liquibase script. Let the DB calculate them for you. That's the whole point of using an identity column, after all.

If you need to reference the ids later in the script somehow, look up the way of obtaining the last inserted id from the RDBMS you're using, quite often there will be a method like LAST_INSERTED_ID(). Or, look up the inserted rows using the name column.

user_10928180

You are using generation type IDENTITY, mapping to data type serial in postgres. When you want to insert permissions created from your admin panel, postgres uses a sequence, starting at 1 from your description and the id 1 through 4 are already used by your manual inserts. Leave the ids out of your manual inserts, or explicitly set ids on your entitities created from your admin panel by eg. querying the current maximum id used in the table. Consider changing the generation type.

user_142472300
Related