Sorry to necro an old thread, but just wanted to pop in here and add the above was very helpful, however I got a malformed array literal error from PSQL when trying to use the method above for specifying an empty array [].
Resolution that worked for me, in case anyone else is struggling with the same issue and winds up here, was to use array[]::varchar[] instead of []
Didn’t work:
execute("ALTER TABLE table_name ALTER COLUMN column_name type varchar(255)[] using string_to_array(column_name, ','), ALTER column_name SET default '[]' ")
Worked:
execute("ALTER TABLE table_name ALTER COLUMN column_name type varchar(255)[] using string_to_array(column_name, ','), ALTER column_name SET default array[]::varchar[]")
My SQL/PSQL skills are very poor however, so maybe I’m just missing something.
