Why am I obtaining this error related the auto increment PK of a PosgreSQL table using Hibernate? ER-CodePudding

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", "[email protected]", "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?

CodePudding user response：

Change

GeneratedValue(strategy=GenerationType.AUTO)

@GeneratedValue(strategy=GenerationType.IDENTITY)

Because GenerationType.IDENTITY create primary key with auto increment and create hibernate_sequence table automatically in your database

CodePudding user response：

The error is definitely on this line.

id bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),

If you want a quick fix, you can change it to the one below. SERIAL works in Postgres just fine. Sure, it might not run on other databases, but how often does an IT project do a DB migration ? Never.

id serial PRIMARY KEY NOT NULL,

Does this solve your problem ? Or you somehow need to solve it without SERIAL for some reason ?