2

I have a 9 character string I want to store in Postgresql (9.3) as character(9). Just as important, I want it to validate correctly if the database is correct, which is not happening. I am generating the schema using the Database Migration plugin, which uses Hibernate tools. Here's what I've tried so far:

static mapping = {
    poleID column: "pole_id", sqlType: "character", length: 9
}

That is exactly how it is created in the DB, however when I do a dbm-gorm-diff, it tries to modify the column thusly:

changeSet(author: "phil (generated)", id: "1401652394765-3") {
    modifyDataType(columnName: "pole_id", newDataType: "character", tableName: "unit")
}

You can see length is ignored. I also tried specifying it with: 

static mapping = {
    poleID column: "pole_id", sqlType: "character(9)"
}

Again, it tries to modify that column, which is already correct, to character(9). How do I specify the mapping so it sees that the DB is already correct?

Philip
  • 697
  • 7
  • 20
  • 1
    Consider using `varchar` and just adding a `CHECK (length(mycol) = 9)` constraint. `character` is a weird data type, and is generally not worth using. – Craig Ringer Jun 02 '14 at 06:42
  • Thanks Craig, I see I missed the fine print where PG offers no performance improvement for using character over varchar (and automatic padding with blanks isn't what I want, either). – Philip Jun 02 '14 at 17:11

1 Answers1

2

I just had a similar problem with MySQL and grails 2.3.7 - I tried to use

sqltype: 'char', length: 128,

but the length was ignored, schema-export showed it as 'char'. In MySQL, char vs. varchar matters, so I worked around it by specifying

sqltype: 'char(128)'

I think it's simply a bug in Grails - unless expressly someone wanted this NOT to work (but then it should be better documented).

Bulba
  • 805
  • 8
  • 10
  • Sounds like length is only for varchar. Question is, when you use char(128) and the DB is correct, does it try to modify the type? This may be an issue with Hibernate tools and not Grails, since I think the former is used to generate the schema by both Grails and the Database Migration plugin. – Philip Jun 11 '14 at 07:11