ERROR: syntax error at or near "user"

37,590

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;
Share:
37,590
Bjorn Roche
Author by

Bjorn Roche

I write audio and video software. I like to work with C and related languages (C++, Go, Java, Objective-C, Swift, etc) but whatever works. Currently I'm working on Shimmeo, a music video app: www.shimmeo.com I can be reached at bjornroche.com

Updated on January 29, 2020

Comments

  • Bjorn Roche
    Bjorn Roche almost 4 years

    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
    
  • Bjorn Roche
    Bjorn Roche almost 12 years
    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
    Bjorn Roche almost 12 years
    This assumes that User is the only class that subclasses PropertyContainer, doesn't it?
  • Bjorn Roche
    Bjorn Roche almost 12 years
    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
    JB Nizet almost 12 years
    No. Hibernate names the join column "user" because it joins to an entity named "User".
  • Bjorn Roche
    Bjorn Roche almost 12 years
    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
    bogdan.rusu almost 8 years
    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
    dhS almost 7 years
    @BjornRoche I think the question is similar to your's question.. could you pls help me on this ... stackoverflow.com/questions/42224949/…
  • M at
    M at almost 3 years
    I had this issue inside entity fixed by @column(name="\"user\"")