27

When I try to persist an entity called "user" with JPA/hibernate it does not work. The table is not created and it is because user is a reserved word in postgresql. Is there any way other than naming the table something else to make this work?

Bozho
  • 588,226
  • 146
  • 1,060
  • 1,140
Piotr
  • 4,813
  • 7
  • 35
  • 46
  • I just ran into this, I thought switchign db vendor with hibernate was supposed to be problem free – NimChimpsky Jan 28 '13 at 16:27
  • 1
    As per http://stackoverflow.com/q/3364835/1266906 you can use hibernate.globally_quoted_identifiers=true in recent versions of hibernate – TheConstructor Feb 14 '15 at 17:30

6 Answers6

30

To quote an identifier, use back ticks:

@Table(name="`users`")

See this example from Hibernate's test suite:

https://github.com/hibernate/hibernate-orm/blob/master/hibernate-core/src/test/java/org/hibernate/test/quote/User.java#L31

Hibernate will automatically detect it and convert to the appropriate quote for the database you are using.

jpkroehling
  • 13,881
  • 1
  • 37
  • 39
20

JPA supports the following syntax for specifying that the tablename must be used exactly as specified: 

@Table(name="\"user\"")

Try using this annotation on your entity class and see if it does the trick. The backslashes are used to escape one set of double-quotes, so it looks kind of ugly.

Jim Tough
  • 14,843
  • 23
  • 75
  • 96
  • Cool, that worked for the table - but the next problem is that tables like user_user_permissions (a collection field in the User class called userPermissions) that gets the same problem for some reason. The reason why I can not set the table name for that field is because it is in a parent class because this is a common field for most of my classes. Any ideas? – Piotr Dec 04 '10 at 11:28
  • Piotr, user_user_permissions should be fine as a table name, what error are you getting? Is one of your columns called "user"? (AFAIK wanting to map the same thing different ways is incompatible with annotations though) – araqnid Dec 06 '10 at 05:11
  • This causes failed POST requests when using a Spring PagingAndSortingRepository of user entity. I'm using postgres. – David Villamizar Jan 28 '19 at 02:51
7

I'd say that you should avoid having table names that are reserved words, with hibernate. Sure you can escape it, but it may cause problems in the future (in a query for example). So the safest way is to name the table another way - say users:

@Entity
@Table(name="users")
public class User {..}
Bozho
  • 588,226
  • 146
  • 1,060
  • 1,140
  • 1
    Not sure I agree. Hibernate is pretty capable of quoting wherever needed. If it doesn't, then it's a bug and should be reported. – jpkroehling Dec 04 '10 at 10:04
  • I agree, you may need to access the database from a non-ORM domain in future, so it is better to avoid using those keywords. – Motolola Aug 19 '19 at 18:45
5

PostgreSQL follows the ANSI standard for quoting object names, so you need to specify "user" as the tablename (including the double quotes)

SELECT *
FROM "user";

I don't know how you would tell hibernate to generate such a statement.

I strongly recommend you find a different name for your table, it will give you more problems that it's worth.

5

As others said, user is a reserved word in SQL and Postgres.

Many databases have many reserved words, over a thousand the last time I tallied. So it is very easy to run into weird problem due to a reserved word collision.

Trailing underscore: user_

Here is the handiest tip I ever learned for SQL: Always append a trailing underscore to your names. I do this for table names, column names, index names, and so on.

The SQL spec specifically promises† to never have a keyword or reserved word with a trailing underscore. This promise is oddly inserted into the spec with no context. But to me it screams out “Append underscore to all your names!”.

After adopting this rule, I discovered a pleasant secondary benefit. When I see the underscore in the code, in the comments, in issue-tracking, and in the emails, I always know we are referring specifically to the database item such as customer_ table versus the concept of “customer” or the class Customer in my Java code.

† I cannot quote the SQL spec because it is copyright protected, unfortunately. In the SQL:2011 spec, read section 5.4 Names and identifiers under the heading Syntax Rules item 3, NOTE 111. In SQL-92 see section 5.2, item 11. Just searching for the word underscore will work.

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
1

You can use schema name to refer to the user table. Use default public schema if you aren't using any specific one.

@Table(name="user", schema="public")
Shekhar Sahu
  • 504
  • 1
  • 6
  • 19