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?
-
I just ran into this, I thought switchign db vendor with hibernate was supposed to be problem freeNimChimpsky– NimChimpsky2013-01-28 16:27:28 +00:00Commented Jan 28, 2013 at 16:27
-
1As per stackoverflow.com/q/3364835/1266906 you can use hibernate.globally_quoted_identifiers=true in recent versions of hibernateTheConstructor– TheConstructor2015-02-14 17:30:01 +00:00Commented Feb 14, 2015 at 17:30
Add a comment
|
6 Answers
To quote an identifier, use back ticks:
@Table(name="`users`")
See this example from Hibernate's test suite:
Hibernate will automatically detect it and convert to the appropriate quote for the database you are using.
1 Comment
jpkroehling
Changed. But one could infer the location by reading the URL, no?
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.
3 Comments
Piotr
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?
araqnid
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)
David Villamizar
This causes failed POST requests when using a Spring PagingAndSortingRepository of user entity. I'm using postgres.
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 {..}
2 Comments
jpkroehling
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.
Motolola
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.
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.
Comments
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.
Comments
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")
