Cannot create a database table named 'user' in PostgreSQL

Question

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

Erythema

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 – Slimsy

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

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

Similar: Using type as database column name – Ol

Answer 1

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

Supersensual

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

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

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

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

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. – Pard

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) – Pard

I would recommend not to use "user" (I mean in PostgreSQL) just like was mentioned, it is a reserved word, and when I used it (I mean with quotes), it caused many useless problems. – Jacintojack

hours of frustration because of this. thank for the answer. – Blackfoot

Caused by: org.hibernate.QueryException: unexpected char: '"' [select generatedAlias0 from "user" as generatedAlias0] – Sylphid

Answer 2

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

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

Flawy

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

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. – Irv

Answer 3

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.

Ol

That's a smart and easy move. – Causerie

this is what real experience in the field looks like – Ambivert

How you name associative tables? – Pert

Answer 4

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.

Scalenus

Did the same thing. – Esemplastic

Answer 5

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

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

Stair