Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Cannot create a database table named 'user' in PostgreSQL

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

like image 530
Channa Avatar asked Mar 07 '14 17:03

Channa


People also ask

Is user a keyword in Postgres?

user is a keyword; "user" is a user-defined identifier that's a legal table name.

Does Postgres have user table?

PostgreSQL: Find Users in PostgreSQL Answer: In PostgreSQL, there is a system table called pg_user. You can run a query against this system table that returns all of the Users that have been created in PostgreSQL as well as information about these Users.

How do you name a user table?

Table names are by convention lowercase and pluralized with multi-word table names separated by underscores. For example, a Model name of Ingredient expects the table name ingredients .


2 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

like image 165
a_horse_with_no_name Avatar answered Sep 26 '22 19:09

a_horse_with_no_name


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

@Table(name="\"user\"") 
like image 24
Nick Avatar answered Sep 22 '22 19:09

Nick