[FIXED] Why this "syntax error at or near "user"" error trying to insert a new record using the save() method from JpaRepository?

Issue

I am working on a Spring Boot project using Spring Data JPA and Hibernate but I am finding some difficulties trying to perform a simple INSERT query using the save() method of the JpaRepository interface.

As database I am using PostgreSQL, here I have the follosing table:

CREATE TABLE IF NOT EXISTS public."User"
(
    id bigint NOT NULL,
    first_name character varying(50) COLLATE pg_catalog."default" NOT NULL,
    middle_name character varying(50) COLLATE pg_catalog."default",
    surname character varying(50) COLLATE pg_catalog."default" NOT NULL,
    sex "char" NOT NULL,
    birthdate date NOT NULL,
    tax_code character varying(255) COLLATE pg_catalog."default" NOT NULL,
    e_mail character varying(255) COLLATE pg_catalog."default" NOT NULL,
    contact_number character varying(50) COLLATE pg_catalog."default" NOT NULL,
    created_at timestamp without time zone NOT NULL,
    CONSTRAINT "User_pkey" PRIMARY KEY (id)
)

that I have mapped into an thie User entity class in my project, this one:

package com.easydefi.users.entity;

import java.io.Serializable;
import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

import lombok.Data;

@Entity
@Table(name = "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;
    
    @Column(name = "sex")
    private char sex;
    
    @Column(name = "birthdate")
    private Date birthdate;
    
    @Column(name = "tex_code")
    private String taxCode;
    
    @Column(name = "e_mail")
    private String eMail;
    
    @Column(name = "contact_number")
    private String contactNumber;
    
    @Column(name = "created_at")
    private Date createdAt;

    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;
        this.sex = sex;
        this.birthdate = birthdate;
        this.taxCode = taxCode;
        this.eMail = eMail;
        this.contactNumber = contactNumber;
        this.createdAt = createdAt;
    }
    
}

Ok, the I created this UserRepository interface extenging the JpaRepository intreface:

public interface UserRepository extends JpaRepository<User, Integer> {

}

At the moment it is empty because I was trying to perform a first test of the save() method.

Finnally I created this simple test class containing a method to test the operation related to the insertion of a new record into the User table, this:

@SpringBootTest()
@ContextConfiguration(classes = GetUserWsApplication.class)
@TestMethodOrder(OrderAnnotation.class)
public class UserRepositoryTest {
    
    @Autowired
    private UserRepository 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 performing this method I obtain the following exception\error in my stacktrace:

2021-11-03 12:19:28.563  WARN 8094 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 42601
2021-11-03 12:19:28.567 ERROR 8094 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: syntax error at or near "user"
  Position: 13
2021-11-03 12:19:32.058  INFO 8094 --- [ionShutdownHook] j.LocalContainerEntityManagerFactoryBean : Closing JPA EntityManagerFactory for persistence unit 'default'
2021-11-03 12:19:32.065  INFO 8094 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
2021-11-03 12:19:32.080  INFO 8094 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.

The problem is that it speack about a generic syntax error at or near "user" but I can not understand what is the problem.

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

Solution

In Postgres, user is a reserved SQL keyword. You can change the name of the table to another word or you can use the "" tricks, but I don't recommend it.

Answered By - devcrypts