How to sync id on hibernate and postgres?

Question

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; 
}

You colud use ids -1, -2, -3 and -4 for the ids, staying out of the way of the sequence, that postgres uses. But @crizzis is right, you should omit the ids. — Turo
– Turo, Commented Nov 29, 2020 at 20:43

crizzis · Accepted Answer · 2020-11-29 20:32:28Z

3

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.

answered Nov 29, 2020 at 20:32

crizzis

10.8k2 gold badges32 silver badges54 bronze badges

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

Comments

jottler189 · Accepted Answer · 2020-11-29 20:46:08Z

0

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.

answered Nov 29, 2020 at 20:46

jottler189

684 bronze badges

Collectives™ on Stack Overflow

How to sync id on hibernate and postgres?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related