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?

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

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.