CODEWITHSUNDEEP
hibernatepostgresqljpa
Piotr
Piotr

Reputation: 4963

Unable to use table named "user" in postgresql hibernate

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?

Upvotes: 28

Views: 19668

Answers (6)

Shekhar Sahu
Shekhar Sahu

Reputation: 573

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")

Upvotes: 2

Basil Bourque
Basil Bourque

Reputation: 339362

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.

Upvotes: 7

jpkroehling
jpkroehling

Reputation: 14061

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.

Upvotes: 31

Bozho
Bozho

Reputation: 597234

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 {..}

Upvotes: 7

Jim Tough
Jim Tough

Reputation: 15240

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.

Upvotes: 20

user330315
user330315

Reputation:

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.

Upvotes: 5

Related Questions