Why am I obtaining this error related the auto increment PK of a PosgreSQL table using Hibernate? ERROR: relation "hibernate_sequence" does not exist

Question

I am working on a Spring Booot application using Spring Data JPA. As database I am using PostgreSQL and I am finding some problem mapping an auto increment primary key field of a table to the related field of my entity class.

In my database I manually created this table:

CREATE TABLE IF NOT EXISTS public."user"
(
    id bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    first_name character varying(50) COLLATE pg_catalog."default" NOT NULL,
    middle_name character varying(50) COLLATE pg_catalog."default" NOT NULL,
    surname character varying(50) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT user_pkey PRIMARY KEY (id)
)

Please not that the id field (my PK) is defined as a bigint with the GENERATED ALWAYS constraint and not as a serial (this because serial data type is deprecated because it is not part of the SQL standard).

Then I created this entity class mapping this table:

package com.easydefi.users.entity;
import java.io.Serializable;
import java.util.Date;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
import lombok.Data;

@Entity
@Table(name = "portal_user")
@Data
public class User implements Serializable {
     
    private static final long serialVersionUID = 5062673109048808267L;
    
    @Id
    @Column(name = "id")
    private int id;
    
    @Column(name = "first_name")
    private String firstName;
    
    @Column(name = "middle_name")
    private String middleName;
    
    @Column(name = "surname")
    private String surname;
    
    public User(String firstName, String middleName, String surname, char sex, Date birthdate, String taxCode,
            String eMail, String contactNumber, Date createdAt) {
        super();
        this.firstName = firstName;
        this.middleName = middleName;
        this.surname = surname;
    }
    

}

Then I have this repository interface (at the moment it is empty because I am testing only the save() method directly provided by JpaRepository):

public interface UsersRepository extends JpaRepository<User, Integer> {

}

Finnally I created this simple unit test method in order to test the insert of a new record via the save() method of my repository:

@SpringBootTest()
@ContextConfiguration(classes = GetUserWsApplication.class)
@TestMethodOrder(OrderAnnotation.class)
public class UserRepositoryTest {
    
    @Autowired
    private UsersRepository userRepository;
    
    
    @Test
    @Order(1)
    public void testInsertUser() {
        User user = new User("Mario", null, "Rossi", 'M', new Date(), "XXX", "xxx@gmail.com", "329123456", new Date());
        
        userRepository.save(user);
        
        assertTrue(true);
        
    }
    
}

the problem is that when the save() method is performed I am obtaining this exception:

Hibernate: 
    insert 
    into
        portal_user
        (first_name, middle_name, surname, id) 
    values
        (?, ?, ?, ?)
2021-11-04 12:17:39.576  WARN 11436 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 428C9
2021-11-04 12:17:39.578 ERROR 11436 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: cannot insert a non-DEFAULT value into column "id"
  Detail: Column "id" is an identity column defined as GENERATED ALWAYS.
  Hint: Use OVERRIDING SYSTEM VALUE to override.

So I try to modify the mapping of the id field into my entity class, in this way:

@Id
@Column(name = "id")
@GeneratedValue(strategy=GenerationType.AUTO)
private int id;

But running my test method now the save() method execution give me this other error:

Hibernate: 
    select
        nextval ('hibernate_sequence')
2021-11-04 12:20:21.133  WARN 11639 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 42P01
2021-11-04 12:20:21.136 ERROR 11639 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: relation "hibernate_sequence" does not exist
  Position: 17

What is the problem? What am I missing? How can I try to fix this issue?

Answer 1

Change

GeneratedValue(strategy=GenerationType.AUTO)

To

@GeneratedValue(strategy=GenerationType.IDENTITY)

Because GenerationType.IDENTITY create primary key with auto increment that is not modified manually.