PostgreSQL migrate column to array

Question

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?

• user1822: 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?
• David Gustavsson: 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?
• user1822: Yes character varying is very different from character ;)

Answer 1

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 ''

• David Gustavsson: 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.
• user1822: @DavidGustavsson sorry, I have no idea what that sequelize thing is.
• David Gustavsson: sequelize - It is an ORM to wok with databases on NodeJS..
• David Gustavsson: 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 ''?
• user1822: @DavidGustavsson: see my update

Answer 2

There was not enough space to write the comment. First of all, I want to thank to @a_horse_with_no_name for his solution provided in his answer. It has some minor bug that is easy to fix.

The bug is the following: If there is null in the x it will be transformed as {null} and not as null. In order to demonstrate the bug, consider the following script:

CREATE TABLE ALX_TEST (
 id integer generated by default as identity (start with 1 cycle),
 name varchar(100),
 PRIMARY KEY (id)
 );
insert into ALX_TEST(name)
values(null);
insert into ALX_TEST(name)
values('');
insert into ALX_TEST(name)
values('Film');
ALTER TABLE ALX_TEST
 ALTER COLUMN NAME TYPE NAME[] USING ARRAY[NAME]);
select * from ALX_TEST order by id;

You will see the first line will have id=1, name='{null}' in it.

The fix is really simple:

ALTER TABLE ALX_TEST
 ALTER COLUMN NAME TYPE NAME[] USING NULLIF(ARRAY[NAME], '{null}');
select * from ALX_TEST order by id;

You will see the first line will have id=1, name=null in it.