USING statement in PG's ALTER COLUMN

test.log

# Create a table
[email protected]/19678=# create table ary(foo text); insert into ary values ('hello world'),('foo bar baz'); select * from ary;
CREATE TABLE
INSERT 0 2
     foo
-------------
 hello world
 foo bar baz
(2 rows)

# Use `USING string_to_array` to separate each word
[email protected]/19678=# alter table ary alter column foo type text[] using string_to_array(foo,' '); select * from ary;
ALTER TABLE
      foo
---------------
 {hello,world}
 {foo,bar,baz}
(2 rows)


# Find out first words and lengths of arrays
[email protected]/19678=# select foo[1], array_length(foo, 1) from ary;
  foo  | array_length
-------+--------------
 hello |            2
 foo   |            3
(2 rows)