1

I have understood the solution for changing the column type from string to text while using postgresql and rails 3.2 provided here. I have also implemented it. But when I rollback this migration, it fails with "PG::StringDataRightTruncation: ERROR: value too long" error. How should we tackle this problem?

Community
  • 1
  • 1
GauravDH
  • 33
  • 7
  • Huh? There is no "string" type in PostgreSQL. If you were converting from `varchar` to `text` then it wouldn't be truncating. Are you trying to go in the other direction? – Craig Ringer Jul 24 '14 at 05:56
  • @CraigRinger I meant varchar in PostgreSQL, string was specified since its used in migration. Migrating up works fine no doubt, but migrating down fails. Need a solution for migrating down(i.e., rollback). – GauravDH Jul 24 '14 at 06:30

1 Answers1

1

You have new values that're too long for the old type. PostgreSQL would have to throw away data to change to varchar(255) if the values are longer than 255 chars. It refuses to do so because it won't cause data loss without being told very specifically to do so.

If you don't mind truncating these long values, permanently and unrecoverably discarding data, you can use the USING clause of ALTER COLUMN ... TYPE. This is the same approach used when converting string columns to integer.

ALTER TABLE mytable 
  ALTER COLUMN mycolumn 
  TYPE varchar(255) 
  USING (substring(mycolumn from 1 for 255));

I don't think there is any way to express this symmetrically in a Rails migration; you will need separate clauses for the up- and down- migrations, with the up-migration being a simple:

ALTER TABLE mytable 
  ALTER COLUMN mycolumn 
  TYPE text;

Frankly though, I think it's a terrible idea to do this in a migration. The migration should fail. This action should require administrator intervention to UPDATE the columns that have data that is too long, then run the migration.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • The problem with making the administration intervention separatedly from the alter table is that running migrations takes time. This is no problem if you can freeze the system. But in a live one, in the time between the administrative task is done and the migration is executed, someone can enter a new value with a longer-than-allowed string, which would result in a failed migration. – kikito Sep 10 '15 at 16:53
  • @kikito Good point. What you need there to add the constraint as `NOT VALID`, make the change, and `ALTER` the constraint to mark it `VALID` and change the type, but unfortunately PostgreSQL only supports `NOT VALID` for `FOREIGN KEY` constraints at the moment. You'd have to use a trigger to enforce the length limit instead. Bit messy, so if you can afford the downtime doing it in one migration makes sense. – Craig Ringer Sep 10 '15 at 22:47