Cannot create a database table named 'user' in PostgreSQL

postgresql hibernate identifier reserved-words

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

Yes, user is a reserved keyword. Just don't use it as a table name. postgresql.org/docs/9.3/static/sql-keywords-appendix.html

You can create a table named 'user' on postgreSQL. But i advice you to avoid using table/column/variables ... names using token keywords

So sorry this is not a duplicate question. Question is about postgresql not about some thing with MySql. Thanks.

krock

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

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 :(

@MaksimGumerov: the proper answer is: don't use names that require quoted identifiers - regardless of the obfuscation layer you are using.

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

@MaksimGumerov the point is that you can't create the table named user because it is reserved by the database.

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.

Nick

It is possible to specify tablename with JPA with next syntax:

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

That solved my problem. Without that I was getting errors like column user0_.id does not exist

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.

a_horse_with_no_name

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.

Did the same thing.

AlexElin

You can create a table user in a schema other than public. The example:

CREATE SCHEMA my_schema;
CREATE TABLE my_schema.user(...);

Cannot create a database table named 'user' in PostgreSQL

Follow WeChat

Want to stay one step ahead of the latest teleworks?

相似问题

Platform

Support

Contact US