4

I've got a situation where I'm trying to create a table called 'user' in Postgres, which throws an error due to Hibernate not putting table names in quotes:

| Error 2012-02-27 23:06:58,782 [Thread-10] ERROR hbm2ddl.SchemaExport  - Unsuccessful: create table user (id int8 not null, version int8 not null, account_expired bool not null, account_locked bool not null, email_address varchar(255) not null, enabled bool not null, first_name varchar(255) not null, last_name varchar(255) not null, mobile_number varchar(255) not null, "password" varchar(255) not null, password_expired bool not null, username varchar(255) not null unique, primary key (id))

This is despite specifying that it should use the PostgreSQLDialect in DataSource.groovy:

dialect = org.hibernate.dialect.PostgreSQLDialect

How can I configure Hibernate to put quotes around table names when dealing with Postgres?

srkiNZ84
  • 3,558
  • 5
  • 30
  • 37
  • 2
    I would strongly recommend to use a different table name. Using reserved words will give you problems all over, not only in Hibernate. –  Feb 27 '12 at 10:13

3 Answers3

8

You can quote the table name in the mapping block with backticks. Hibernate will convert those to whatever the quoting approach is for the database based on the Dialect:

static mapping = {
    table "`user`"
}

You can also just rename the table to something else that doesn't require quoting/escaping, e.g.

static mapping = {
    table "users"
}
Burt Beckwith
  • 75,342
  • 5
  • 143
  • 156
4

This answer might help you: https://stackoverflow.com/a/3611916/947357 It shows how to quote table names in Hibernate and in JPA.

Community
  • 1
  • 1
A.H.
  • 63,967
  • 15
  • 92
  • 126
1

Assuming you're follwing the same tutorial as I do ("Getting started with Grails" by InfoQ) you've already understood it doesn't provide any coverage to PostgreSQL. Based on this post:

  1. "User" is a reserved word in Postgres.
  2. The mapping should be added into the class itself (it's not absolutely obvious):
class User { 
   String username 
   String password 
   ... 
   static mapping = { 
      table 'users' 
      password column: '`password`' 
   } 
}

or

class User { 
   String username 
   String password 
   ... 
   static mapping = { 
      table '`user`' 
      password column: '`password`' 
   } 
}
evictorov
  • 33
  • 1
  • 12