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

@TJ, I added the errors which lead me to believe it's still using the column name. — Bjorn Roche, Jan 27 '12 at 16:02

score 76 · Answer 1 · edited May 23 '17 at 12:17

76

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?

edited May 23 '17 at 12:17

Community

1
1

answered Jan 27 '12 at 16:26

filiprem

6,721
1
29
42

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. – Bjorn Roche Jan 27 '12 at 16:38
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. – bogdan.rusu Feb 26 '16 at 10:06
@BjornRoche I think the question is similar to your's question.. could you pls help me on this ... http://stackoverflow.com/questions/42224949/exception-org-postgresql-util-psqlexception-error-syntax-error-at-or-near-c?noredirect=1#comment71610583_42224949 – dhS Feb 14 '17 at 12:01
I had this issue inside entity fixed by `@column(name="\"user\"")` – M at Mar 15 '21 at 19:08

score 6 · Answer 2 · answered Jan 27 '12 at 16:08

6

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 {

answered Jan 27 '12 at 16:08

esaj

15,875
5
38
52

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 Jan 27 '12 at 16:14

score 4 · Accepted Answer · answered Jan 28 '12 at 23:52

4

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

answered Jan 28 '12 at 23:52

Bjorn Roche

11,279
6
36
58

score 1 · Answer 4 · answered Jan 27 '12 at 16:14

1

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;

answered Jan 27 '12 at 16:14

JB Nizet

678,734
91
1,224
1,255

This assumes that User is the only class that subclasses PropertyContainer, doesn't it? – Bjorn Roche Jan 27 '12 at 16:17
No. Hibernate names the join column "user" because it joins to an entity named "User". – JB Nizet Jan 28 '12 at 09:31
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? – Bjorn Roche Jan 28 '12 at 19:34

ERROR: syntax error at or near "user"

4 Answers4