ERROR: syntax error at or near "user"

Question

I'm using Hibernate and have a persistent class called "User". Since this is a keyword, I marked the @Entity attribute with a different name (I have seen, for example, this question: Unable to use table named "user" in postgresql hibernate)

However, I still run into trouble because this class extends another, and it looks like hibernate is still trying to use "user" as a column name and getting messed up:

@Entity( name = "XonamiUser" )
public class User extends PropertyContainer {
    ...

and

@MappedSuperclass
public abstract class PropertyContainer implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    protected long key;
    /** tags */
    @ElementCollection
    protected Set<String> tags;
    @ElementCollection
    protected Set<String> searchList;
    ...

My other classes that extend PropertyContainer seem to work fine.

Is this a bug in hibernate? Is there some way around this short of refactoring? Thanks!

Here are the errors I'm seeing (slightly cleaned up):

WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: 0, SQLState: 42601
ERROR org.hibernate.util.JDBCExceptionReporter - Batch entry 0 insert into user_search_list (user, search_list) values ('1', 'bjorn') was aborted.  Call getNextException to see the cause.
WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: 0, SQLState: 42601
ERROR org.hibernate.util.JDBCExceptionReporter - ERROR: syntax error at or near "user"
  Position: 31

Solution 1

In the end, I refactored the class user to XonamiUser. This wasn't quite what I wanted, but it worked great.

Solution 2

user is a reserved keyword in PostgreSQL. It is allowed only as quoted identifier.

You have to force Hibernate to use quoted "user" in this INSERT command.

I'm not Hibernate expert but maybe this Hibernate saving User model to Postgres will help?

Solution 3

I'm a bit confused are you referring to a column or table named User... You can use the @Table-annotation to set the table name for the entity in JPA:

@Entity
@Table(name = "XonamiUser")
public class User extends PropertyContainer {

Solution 4

The Hibernate reference manual has an exemple showing how to define the table name used to hold the elements of the collection. And it happens to use a User entity to do so, and to define the column as user_id rather than user.

This mapping should be OK, for your case:

@ElementCollection
@CollectionTable(name = "user_search_list", 
                 joinColumns = @JoinColumn(name = "user_id"))
@Column(name = "search_list")
protected Set<String> searchList;

View more solutions

Comments

[Bjorn Roche]

I get "Unsuccessful: create table user_search_list (user int8 not null, search_list varchar(255))... ERROR org.hibernate.tool.hbm2ddl.SchemaUpdate - ERROR: syntax error at or near "user"

[Bjorn Roche]

This assumes that User is the only class that subclasses PropertyContainer, doesn't it?

[Bjorn Roche]

yes, the question I linked to in my original post makes the same point. This is the reason I used the @Entity( name = "XonamiUser" ) annotation.

[JB Nizet]

No. Hibernate names the join column "user" because it joins to an entity named "User".

[Bjorn Roche]

but searchList belongs to PropertyContainer, so User, and other classes inherit it. If I tell hibernate that it should use the name user_search_list, then won't it try to use that name for all classes that inherit?

[bogdan.rusu]

My error was sth like ERROR: syntax error at or near. Hibernate overwrites the actual error of Postgresql for reserved word. It took me a while to figure it out.

[dhS]

@BjornRoche I think the question is similar to your's question.. could you pls help me on this ... stackoverflow.com/questions/42224949/…

[M at]

I had this issue inside entity fixed by @column(name="\"user\"")