Syntax error when creating table in Postgresql using Hibernate

Question

    @Entity
public class User {
    @Id
    @GeneratedValue
    private int id;
    private String name;
    private String email;
    private String sex;
    private int age;
    private Date birthday;
    private Date registerdate;
    private String login;
    private String password;
    @ManyToMany
    @JoinTable(name = "eventlike_user", joinColumns = @JoinColumn(name = "id_user"), inverseJoinColumns = @JoinColumn(name = "id_eventlike"))
    private Collection<Eventlike> eventlikes;
    @OneToMany
    private Collection<Event> events;

   //getters and setters

I get the following error when creating tables:

ERROR: HHH000388: Unsuccessful: create table User (id int8 not null, Birthday timestamp, email varchar(255), login varchar(255), name varchar(255), password varchar(255), registerdate timestamp, sex varchar(255), Primary key (id)) Nov 12, 2014 10:55:33 AM org.hibernate.tool.hbm2ddl.Schemaupdate execute ERROR: ERROR: Error syntax on or near "User" Position: 14

I don’t know what’s making this table not be created but what strikes me is this guy int8 of id.

Answer 1

This error happens because accented characters, uppercase letters and reserved words must be escaped with double quotation marks ". In your case user is a reserved word. According to this reply from Soen your note should look like this:

For JPA 1.0

@Entity
@Table(name="`User`")
public class User {
    ...
}

JPA 2.0

@Entity
@Table(name="\"User\"")
public class User {
    ...
}

Answer 2

Postgresql has the convention to use lowercase characters to represent identifiers, i.e., object names (schemas, tables, views, functions, procedures). It’s good practice to follow this.

When declaring objects with uppercase letters, we have to use quotation marks. For example: create table "User".

As far as I have researched, Hibernate does not treat these differences transparently. Maybe it even has some parameter.

You can try adding the annotation @Table in its class by specifying the lower case name. Example:

@Entity
@Table(name="user")
public class User { 
    ...
}

In addition to this change, check if the table already exists in the bank and, if so, normalize its name to lowercase. Do this for all tables.