Change column data type from text to array by splitting on delimiter

Question

I need to change column data type. It contains text in form "part1 part2 part3".

I want its contents to be represented as an array (["part1", "part2", "part3"]).

What is the easiest way to do that? Do I need to write a program or is it possible to do using only SQL?

I am using PostgreSQL.

Answer 1

If you just want to select that as an array:

select string_to_array(your_column, ' ')
from your_table;

If you permanently want to change the data type to e.g text[] use this:

alter table your_table 
      alter your_column type text[] using string_to_array(your_column, ' ');