GRAILS with PostgreSQL Gotcha

Everyone is talking about PostgreSQL these days. Especially folks that discover that they are running MySQL illligaly because it´s libs are under the GPL ).

In case you want to check out GRAILS in conjunction with PostgreSQL (gosh – what a name) save yourself some time and don´t call your user domain model ‘user’ because it will fail on database deployment.

column notation .id applied to type “name”, which is not a composite type

That is because User is a reserved table name for PostgreSQL. So call it Person or what not and it will work just fine.

Here is the complete DataSource file to get you up and running quickly:

dataSource {
pooled = false
url = "jdbc:postgresql://localhost:5432/dbDev"
driverClassName = "org.postgresql.Driver"
dialect = org.hibernate.dialect.PostgreSQLDialect
username = "user"
password = "passwordd"
}
hibernate {
cache.use_second_level_cache=true
cache.use_query_cache=true
cache.provider_class='com.opensymphony.oscache.hibernate.OSCacheProvider'
}
// environment specific settings
environments {
development {
dataSource {
dbCreate = "create-drop" // one of 'create', 'create-drop','update'
url = "jdbc:postgresql://localhost:5432/dbDev"
}
}
test {
dataSource {
dbCreate = "update"
url = "jdbc:postgresql://localhost:5432/dbTest"
}
}
production {
dataSource {
dbCreate = "update"
url = "jdbc:postgresql://localhost:5432/dbProd"
}
}
}