It seems PostgreSQL does not allow to create a database table named 'user'. But MySQL will allow to create such a table.
Is that because it is a key word? But Hibernate cannot identify any issue (even if we set the PostgreSQLDialect).
5 Answers
user is a reserved word and it's usually not a good idea use reserved words for identifiers (tables, columns).
If you insist on doing that you have to put the table name in double quotes:
create table "user" (...);
But then you always need to use double quotes when referencing the table. Additionally the table name is then case-sensitive. "user" is a different table name than "User".
If you want to save yourself a lot of trouble use a different name. users, user_account, ...
More details on quoted identifiers can be found in the manual: http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
9 Comments
Maksim Gumerov
Unfortunately this is not an answer in a Hibernate-based project, as the topicstarter mentioned =) The actual problem here is Hibernate's failure to do that sort of escaping automatically. Despite being told the proper dialect! So the proper answer should say describe how to make in work in Hibernate, not in PSQL :(
Maksim Gumerov
Nope that's not the one :) You'll end up tying your code to specific database and its keywords (and this set is subject to changes). One actual solution is turn on escaping globally (did not try it myself though), another is rely on Hibernate dialect analyzers (but as we see, they don't always do their job in determining whether we need to escape something).
Maksim Gumerov
Sure I can - using quotes, for one. Maybe I should not but even that is arguable. What's more important is HOW do I create such a table, and the solution proposed here (one with quotation) will not help in Hibernate projects. And the initial question mentions Hibernate, so the answer actually does not answer it well enough IMO.
Maksim Gumerov
What's even more important (though non-constructive) is, JPA was supposed to let us not think about dialects when writing our entity classes, and here we are talking about how I cannot use a table name for Postgres that's completely valid for Mysql. Tada. (And that's despite the fact JPA could do necessary name escaping for us: it just fails to)
|
It is possible to specify tablename with JPA with next syntax:
@Table(name="\"user\"")
2 Comments
James Freitas
That solved my problem. Without that I was getting errors like
column user0_.id does not exist
Israelm
This one worked for me, is so weird that user is a reserved word since it is used as a table name for almost every application I have worked with during my entire career.
Trailing underscore _
The SQL standard explicitly promises to never use a trailing underscore (U+005F LOW LINE) in any name, keyword, or reserved word. To see for yourself, search the spec for the word “underscore”.
So, to avoid conflicts with any of the over a thousand keywords and reserved words used by various database engines, I name all my database identifiers with a trailing underscore. (Yes, really, over a thousand keywords reserved — I counted them.)
Change this:
CREATE TABLE user ( … ) ;
… to this:
CREATE TABLE user_ ( … ) ;
I do this as a habit for all database names: schemas, tables, columns, indexes, etc.
I rest a bit easier knowing I’ve eliminated an entire class of bugs and problems caused by naming collisions.
As an extra benefit, this practice makes quite clear in documentation, email, and such when referring to a programming language variable named user versus the database column user_. Anything with a trailing underscore is obviously from the database side.
4 Comments
Alexander Ivanchenko
That's a smart and easy move.
winwin
this is what real experience in the field looks like
Dante
How you name associative tables?
Basil Bourque
@Dante I name Associative tables in the same way: As a singular noun (or verb) with a trailing underscore. Examples:
[book_]—<[authorship_]>—[author_] and [student_]—<[registration_]>—[course_]We had this same issue time ago, and we just changed the table name from user to app_user. Due to the use of Hibernate/JPA. We thought it would be easier this way.
Hope this little fix will help someone else.
2 Comments
Stefan Falk
Did the same thing.
Yao Liu
Maybe even change the user class to AppUser to avoid the naming collision with Spring Security User?
You can create a table user in a schema other than public.
The example:
CREATE SCHEMA my_schema;
CREATE TABLE my_schema.user(...);
typeas database column name