4

I got a table in PostgreSQL containing a column 'X' of type character (255). Now I would like to migrate that table so that 'X' changes to type character[] (255), but I want the columns original character (255) value to be in the migrated column as an element of the new array in 'X'.

Using sequelize, is there a way to do that through a migration script? Or is there a way to do it in JavaScript with transitions and stuff?

What would be the best (or maybe only) way to go?

  • 2
    Unrelated, but: are you aware that a maximum length of 255 is by no means more efficient than e.g. 42, 392 or 783? The character data type is a bad choice to begin with - do you really need all values to be blank padded to a length of 255? Even a single character? – a_horse_with_no_name Apr 18 '18 at 12:49
  • It is defined in sequelize as 'DataTypes.STRING', so 255 is not something that I have set personally. But then in pgAdmin, the column is listed as character varying (255). I did leave out the varying part, thinking it was redundant. Maybe that is what you are requesting? – David Gustavsson Apr 18 '18 at 12:58
  • 1
    Yes character varying is very different from character ;) – a_horse_with_no_name Apr 18 '18 at 13:04
7

The following DDL will convert the column to an array and make the existing value be the first array element:

alter table the_table
   alter column x type varchar(255)[] using array[x];

varchar is a synonym for character varying

To reverse this change, you can apply the same logic:

alter table the_table
   alter column x type varchar(255) using coalesce(x[1],'');

x[1] returns the first element, but will only return null if the array is empty. With coalesce() this can then be turned into an empty string ''

  • Great. This works as expected, thanks. But is this possible to do through sequelize? There is a function queryInterface.changeColumn that is able to change the type, but the docs does not state how (if possible) to do it while transferring the value. As all of our other migrations are done through sequelize, it would be good to do this one through sequelize as well. – David Gustavsson Apr 18 '18 at 13:45
  • @DavidGustavsson sorry, I have no idea what that sequelize thing is. – a_horse_with_no_name Apr 18 '18 at 13:47
  • sequelize - It is an ORM to wok with databases on NodeJS.. – David Gustavsson Apr 18 '18 at 13:52
  • Thanks to your input, I was able to run a raw query through sequelize, and it executed perfectly. But as a follow up question while I got your attention: how would I revert this through DDL? Say that I would have an array with 0-n varchar's, and I would like to convert it back to a single varchar using the first element in the current aray, and if the array is empty, setting ''? – David Gustavsson Apr 18 '18 at 14:21
  • @DavidGustavsson: see my update – a_horse_with_no_name Apr 18 '18 at 14:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.