25

I'm using Postgres via Hibernate (annotations), but it seems to be falling over dealing with a User object:

12:09:16,442 ERROR [SchemaExport] Unsuccessful: create table User (id  bigserial not null, password varchar(255), username varchar(255), primary key (id))
12:09:16,442 ERROR [SchemaExport] ERROR: syntax error at or near "User"

If I run the SQL manually I have to put quotes around the table name as user seems to be a postgres keyword, but how can I convince hibernate to do this itself?

Thanks in advance.

Pascal Thivent
  • 562,542
  • 136
  • 1,062
  • 1,124
rich
  • 18,987
  • 11
  • 75
  • 101
  • 1
    It is usually a good practice to avoid names like that – Denys Kniazhev-Support Ukraine Aug 31 '10 at 11:18
  • ....especially if you switch databases regularly, since what is an identifier in the one DB could be a keyword in another. Good style would be pre/suffixes which relate to your application: "myapp_user" as a tablename instead of "user"... – fasseg Aug 31 '10 at 13:08

2 Answers2

62

You need to escape the table name when using reserved keywords. In JPA 1.0, there is no standardized way and the Hibernate specific solution is to use backticks:

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

In JPA 2.0, the standardized syntax looks like this:

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

References

Pascal Thivent
  • 562,542
  • 136
  • 1,062
  • 1,124
  • 1
    Is there any reason why Hibernate doesn't quote table names by default? I can't think of any situation where this wouldn't be useful. – Korbi Dec 11 '11 at 10:05
  • 2
    @Mr.Omsn: Quoting by default is off because the JPA query language is to be translated into plain SQL. And in SQL identifiers like table names and column names are case __in__-sensitive. So SQL would consider `user` and `User` to be the same table. __BUT__ `"User"` and `"user"` would be different tables which can also exist at the same time. The only difference between PostgreSQL and the SQL standard is: PG folds to lower case but SQL to uppercase. – A.H. Feb 27 '12 at 11:13
3

User is a key word, find a better name or use quotes: "User". (bad idea imho, but it works if you do it everywhere)

Frank Heikens
  • 117,544
  • 24
  • 142
  • 135